PostgreSQL 9.3 नया क्या है?

हैलो, habracheloveki! बहुत समय पहले नहीं, PostgreSQL 9.3 का विमोचन हुआ था और मैं आपको ग्राहक भाग के बारे में सबसे महत्वपूर्ण नवाचारों से परिचित कराना चाहूंगा, जो आपके लिए उपयोगी हो सकते हैं। यह लेख निम्नलिखित पर चर्चा करता है:

• भौतिक प्रतिनिधित्व
• अद्यतन विचार
• घटना ट्रिगर
• पुनरावर्ती प्रतिनिधित्व
• पार्श्व पालन
• परिवर्तनशील बाहरी तालिकाओं
• JSON प्रकार के साथ काम करने के लिए कार्य और ऑपरेटर

भौतिकवादी विचार

भौतिकवादी दृश्य एक भौतिक डेटाबेस ऑब्जेक्ट है जिसमें क्वेरी के परिणाम होते हैं। निस्संदेह सबसे प्रत्याशित नवाचारों में से एक है। आइए देखें कि PostgreSQL में इसके साथ कैसे काम करें।

लेखक की एक निर्देशिका और लेखक की एक लिंक के साथ पुस्तकों की एक निर्देशिका बनाएँ:

CREATE TABLE author ( id serial NOT NULL, first_name text NOT NULL, last_name text NOT NULL, CONSTRAINT pk_author_id PRIMARY KEY ( id ), CONSTRAINT uk_author_name UNIQUE ( first_name, last_name ) ); CREATE TABLE book ( id serial NOT NULL, author_id integer NOT NULL, name text NOT NULL, CONSTRAINT pk_book_id PRIMARY KEY ( id ), CONSTRAINT fk_book_author_id FOREIGN KEY ( author_id ) REFERENCES author ( id ), CONSTRAINT uk_book_name UNIQUE ( author_id, name ) ); 

इन तालिकाओं को डेटा से भरें - लेखकों की एक जोड़ी जोड़ें और कई, कई किताबें उत्पन्न करें:

 INSERT INTO author ( first_name, last_name ) VALUES ( '', '' ); --  id = 1 INSERT INTO author ( first_name, last_name ) VALUES ( '', '' ); --  id = 2 INSERT INTO book ( author_id, name ) VALUES ( 1, '   ( ' || generate_series ( 1, 100000 ) || ')' ); INSERT INTO book ( author_id, name ) VALUES ( 2, ' ' ), ( 2, ' ' ); 

तुलना के लिए, हम एक नियमित और भौतिक दृश्य तैयार करेंगे (ध्यान दें कि बाद को बनाने के लिए, परिणाम का चयन करने और रिकॉर्ड करने में थोड़ा अधिक समय लगता है):

 CREATE VIEW vw_book AS SELECT book.id, author.first_name || ' ' || author.last_name AS author_name, book.name FROM book INNER JOIN author ON author.id = book.author_id; CREATE MATERIALIZED VIEW mvw_book AS SELECT book.id, author.first_name || ' ' || author.last_name AS author_name, book.name FROM book INNER JOIN author ON author.id = book.author_id; 

अब, एक नियमित और भौतिक दृष्टिकोण के लिए शर्त के साथ क्वेरी योजना पर एक नज़र डालते हैं:

 EXPLAIN ANALYZE SELECT * FROM vw_book WHERE author_name = ' '; -- Hash Join (cost=24.58..2543.83 rows=482 width=119) (actual time=19.389..19.390 rows=2 loops=1) Hash Cond: (book.author_id = author.id) -> Seq Scan on book (cost=0.00..2137.02 rows=100002 width=59) (actual time=0.017..9.231 rows=100002 loops=1) -> Hash (cost=24.53..24.53 rows=4 width=68) (actual time=0.026..0.026 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on author (cost=0.00..24.53 rows=4 width=68) (actual time=0.019..0.020 rows=1 loops=1) Filter: (((first_name || ' '::text) || last_name) = ' '::text) Rows Removed by Filter: 1 Total runtime: 19.452 ms EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = ' '; -- Seq Scan on mvw_book (cost=0.00..2584.03 rows=7 width=77) (actual time=15.869..15.870 rows=2 loops=1) Filter: (author_name = ' '::text) Rows Removed by Filter: 100000 Total runtime: 15.905 ms 

भौतिकवादी दृश्य के लिए डेटा को ढेर कर दिया जाता है और विभिन्न तालिकाओं से एकत्र नहीं करना पड़ता है। लेकिन यह सब नहीं है, क्योंकि उनके पास इंडेक्स बनाने की क्षमता है। परिणाम में सुधार:

 CREATE INDEX idx_book_name ON mvw_book ( author_name ); EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = ' '; -- Index Scan using idx_book_name on mvw_book (cost=0.42..8.54 rows=7 width=77) (actual time=0.051..0.055 rows=2 loops=1) Index Cond: (author_name = ' '::text) Total runtime: 0.099 ms 

बुरा नहीं है, खोज सूचकांक द्वारा संचालित की जाती है और खोज समय में काफी कमी आई है।

लेकिन भौतिक विचारों का उपयोग करते समय एक अति सूक्ष्म अंतर है - तालिकाओं पर डीएमएल संचालन के बाद जो दृश्य बनाते हैं, दृश्य को प्रस्तुत करना पड़ता है:

 INSERT INTO book ( author_id, name ) VALUES ( 2, '  ' ); REFRESH MATERIALIZED VIEW mvw_book; 

इसे ट्रिगर के साथ स्वचालित किया जा सकता है:

 CREATE OR REPLACE FUNCTION mvw_book_refresh ( ) RETURNS trigger AS $BODY$ BEGIN REFRESH MATERIALIZED VIEW mvw_book; RETURN NULL; END $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER tr_book_refresh AFTER INSERT OR UPDATE OR DELETE ON book FOR EACH STATEMENT EXECUTE PROCEDURE mvw_book_refresh ( ); CREATE TRIGGER tr_author_refresh AFTER INSERT OR UPDATE OR DELETE ON author FOR EACH STATEMENT EXECUTE PROCEDURE mvw_book_refresh ( ); 

हालाँकि पोस्टग्रेसीक्यू 9.2 में कार्यक्षमता का अनुकरण भौतिक रूप से किया जा सकता है (तालिका बनाकर, इसे अनुक्रमित करके और एक ट्रिगर जो एक कठिन क्वेरी को निष्पादित करेगा), लेकिन सामान्य तौर पर यह एक सुविधाजनक नवाचार है।

अद्यतित दृश्य

डीएमएल संचालन को अद्यतन विचारों पर लागू किया जा सकता है। सच है, इस तरह के प्रतिनिधित्व के लिए आवश्यकताएं अधिक हैं: FROM सूची में केवल एक इकाई (तालिका, दृश्य), बिना, DISTINCT , GROUP BY , HAVING , LIMIT और OFFSET विवरणों के बिना, सेट ( UNION , INTERSECT और EXCEPT ) और क्षेत्रों पर संचालन के बिना। कोई कार्य या संचालन लागू नहीं किया जाना चाहिए।

कार्रवाई में अद्यतन विचार:

 CREATE TABLE employee ( id serial NOT NULL, fullname text NOT NULL, birthday date, salary numeric NOT NULL DEFAULT 0.0, CONSTRAINT pk_employee_id PRIMARY KEY ( id ), CONSTRAINT uk_employee_fullname UNIQUE ( fullname ), CONSTRAINT ch_employee_salary CHECK ( salary >= 0.0 ) ); INSERT INTO employee ( fullname, salary ) VALUES ( ' ', 800.0 ); INSERT INTO employee ( fullname, salary ) VALUES ( ' ', 2000.0 ); INSERT INTO employee ( fullname, salary ) VALUES ( '', 1500.0 ); CREATE VIEW vw_employee_top_salary AS SELECT employee.fullname AS name, employee.salary FROM employee WHERE employee.salary >= 1000.0; --    INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( ' ', 2500.0 ); UPDATE vw_employee_top_salary SET salary = 2200.0 WHERE name = ' '; DELETE FROM vw_employee_top_salary WHERE name = ''; --   SELECT * FROM vw_employee_top_salary; 

कृपया ध्यान दें कि INSERT को किसी भी स्थिति में और UPDATE और DELETE में देखा जा सकता है - केवल जब आधार तालिका से सेट दृश्य में स्थिति के अंतर्गत आता है:

 INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( '', 0.0 ); --   UPDATE vw_employee_top_salary SET salary = 3000.0 WHERE name = ''; --   ,   salary  0.0 DELETE FROM vw_employee_top_salary WHERE name = ''; --   ,   salary  0.0 

प्रस्तुत नियमों का उपयोग करके अधिक उन्नत चीजें की जा सकती हैं ।

इवेंट ट्रिगर

इसके अलावा, एक अपेक्षित नवाचार। किसी DB में DDL कमांड को इंटरसेप्ट करने की अनुमति दें। वे पहली बार में सामान्य ट्रिगर्स से भिन्न होते हैं कि वे एक विशिष्ट तालिका के संदर्भ के बिना, वैश्विक हैं, लेकिन आप यह निर्दिष्ट कर सकते हैं कि किसको जवाब देना है।

निम्नानुसार बनाया गया:

 CREATE OR REPLACE FUNCTION event_trigger_begin ( ) RETURNS event_trigger AS $BODY$ BEGIN RAISE NOTICE '(begin) tg_event = %, tg_tag = %', TG_EVENT, TG_TAG; END; $BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION event_trigger_end ( ) RETURNS event_trigger AS $BODY$ BEGIN RAISE NOTICE '(end) tg_event = %, tg_tag = %', TG_EVENT, TG_TAG; END; $BODY$ LANGUAGE plpgsql; CREATE EVENT TRIGGER tr_event_begin ON ddl_command_start EXECUTE PROCEDURE event_trigger_begin ( ); CREATE EVENT TRIGGER tr_event_end ON ddl_command_end EXECUTE PROCEDURE event_trigger_end ( ); 

हम टेबल के साथ अलग-अलग डीडीएल- जोड़तोड़ करते हैं:

 CREATE TABLE article ( id SERIAL NOT NULL, name text NOT NULL, CONSTRAINT pk_article_id PRIMARY KEY ( id ), CONSTRAINT uk_article_name UNIQUE ( name ) ); ALTER TABLE article ADD COLUMN misc numeric; ALTER TABLE article ALTER COLUMN misc TYPE text; ALTER TABLE article DROP COLUMN misc; DROP TABLE article; 

आउटपुट इस तरह होना चाहिए:

 tg_event = ddl_command_start, tg_tag = CREATE TABLE tg_event = ddl_command_end, tg_tag = CREATE TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = DROP TABLE tg_event = ddl_command_end, tg_tag = DROP TABLE 

Plpgsql के माध्यम से, केवल ईवेंट ( TG_EVENT ) और वास्तव में, टीम ( TG_TAG ) के बारे में जानकारी उपलब्ध है, लेकिन मुझे उम्मीद है कि यह भविष्य में बेहतर होगा।

पुनरावर्ती विचार

यदि आपको इस पर एक दृश्य बनाने की आवश्यकता है, तो वे RECURSIVE निर्माण को सरल बनाते हैं।

एक तालिका बनाएं और इसे परीक्षण डेटा के साथ भरें:

 CREATE TABLE directory ( id serial NOT NULL, parent_id integer, name text NOT NULL, CONSTRAINT pk_directory_id PRIMARY KEY ( id ), CONSTRAINT fk_directory_parent_id FOREIGN KEY ( parent_id ) REFERENCES directory ( id ), CONSTRAINT uk_directory_name UNIQUE ( parent_id, name ) ); INSERT INTO directory ( parent_id, name ) VALUES ( NULL, 'usr' ); --  id = 1 INSERT INTO directory ( parent_id, name ) VALUES ( 1, 'lib' ); INSERT INTO directory ( parent_id, name ) VALUES ( 1, 'include' ); INSERT INTO directory ( parent_id, name ) VALUES ( NULL, 'var' ); --  id = 4 INSERT INTO directory ( parent_id, name ) VALUES ( 4, 'opt' ); --  id = 5 INSERT INTO directory ( parent_id, name ) VALUES ( 5, 'tmp' ); INSERT INTO directory ( parent_id, name ) VALUES ( 4, 'log' ); --  id = 7 INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'samba' ); INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'news' ); 

पुनरावर्ती और समान के साथ एक पुनरावर्ती प्रतिनिधित्व के माध्यम से एक प्रश्न:

 WITH RECURSIVE vw_directory ( id, parent_id, name, path ) AS ( SELECT id, parent_id, name, '/' || name FROM directory WHERE parent_id IS NULL AND name = 'var' UNION ALL SELECT d.id, d.parent_id, d.name, t.path || '/' || d.name FROM directory d INNER JOIN vw_directory t ON d.parent_id = t.id ) SELECT * FROM vw_directory ORDER BY path; CREATE RECURSIVE VIEW vw_directory ( id, parent_id, name, path ) AS SELECT id, parent_id, name, '/' || name FROM directory WHERE parent_id IS NULL AND name = 'var' UNION ALL SELECT d.id, d.parent_id, d.name, t.path || '/' || d.name FROM directory d INNER JOIN vw_directory t ON d.parent_id = t.id; SELECT * FROM vw_directory ORDER BY path; 

वास्तव में, एक पुनरावर्ती दृश्य RECURSIVE के साथ एक आवरण है, जिसे केंद्रित दृश्य के पाठ को देखकर देखा जा सकता है:

 CREATE OR REPLACE VIEW vw_directory AS WITH RECURSIVE vw_directory(id, parent_id, name, path) AS ( SELECT directory.id, directory.parent_id, directory.name, '/'::text || directory.name FROM directory WHERE directory.parent_id IS NULL AND directory.name = 'var'::text UNION ALL SELECT d.id, d.parent_id, d.name, (t.path || '/'::text) || d.name FROM directory d JOIN vw_directory t ON d.parent_id = t.id ) SELECT vw_directory.id, vw_directory.parent_id, vw_directory.name, vw_directory.path FROM vw_directory; 

पार्श्व पालन

किसी सबक्वेरी से बाहरी क्वेरी से संस्थाओं तक पहुँचने की अनुमति देता है। उदाहरण उदाहरण (सार्वजनिक स्कीमा से केवल संस्थाओं के लिए फ़ील्ड की संख्या की गिनती):

 SELECT t.table_schema || '.' || t.table_name, q.columns_count FROM information_schema.tables t, LATERAL ( SELECT sum ( 1 ) AS columns_count FROM information_schema.columns c WHERE t.table_schema IN ( 'public' ) AND t.table_schema || '.' || t.table_name = c.table_schema || '.' || c.table_name ) q ORDER BY 1; 

परस्पर बाहरी तालिकाओं

नया postgres_fdw मॉड्यूल, जो आपको किसी अन्य डेटाबेस में स्थित डेटा तक पढ़ने / लिखने की अनुमति देता है। पहले, इस तरह की कार्यक्षमता dblink में थी , लेकिन postgres_fdw में सब कुछ अधिक पारदर्शी, मानकीकृत सिंटैक्स है और आप बेहतर प्रदर्शन प्राप्त कर सकते हैं। आइए देखें कि कैसे postgres_fdw का उपयोग किया जा सकता है।

इसमें एक नया fdb डेटाबेस और एक परीक्षण तालिका बनाएं (यह वर्तमान डेटाबेस के लिए बाहरी होगा):

 CREATE TABLE city ( country text NOT NULL, name text NOT NULL, CONSTRAINT uk_city_name UNIQUE ( country, name ) ); 

चलो वर्तमान डेटाबेस पर वापस जाते हैं और बाहरी डेटा स्रोत को कॉन्फ़िगर करते हैं:

 --   CREATE EXTENSION postgres_fdw; --    CREATE SERVER fdb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', dbname 'fdb' ); --   CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS ( password 'pwd' ); --    CREATE FOREIGN TABLE fdb_city ( country text, name text ) SERVER fdb_server OPTIONS ( table_name 'city' ); 

अब हम बाहरी तालिका के साथ काम कर सकते हैं:

 --   INSERT INTO fdb_city ( country, name ) VALUES ( 'USA', 'Las Vegas' ); --   UPDATE fdb_city SET name = 'New Vegas' WHERE name = 'New Vegas'; -- ,   SELECT * FROM fdb_city; 

यह सुनिश्चित करने के लिए कि डेटा वास्तव में सही जगह पर है, fdb डेटाबेस पर जाएँ और जाँच करें:

 SELECT * FROM city; 

JSON प्रकार के साथ काम करने के लिए कार्य और ऑपरेटर

JSON प्रकार PostgreSQL 9.2 में दिखाई दिया, लेकिन केवल दो फ़ंक्शन थे - array_to_json ( JSON के लिए एक सरणी परिवर्तित करना) और row_to_json ( JSON के लिए एक रिकॉर्ड परिवर्तित करना)। अब और कार्य हैं और आप इस प्रकार से पूरी तरह से काम कर सकते हैं:

 CREATE TYPE t_link AS ( "from" text, "to" text ); CREATE TABLE param ( id serial NOT NULL, name text NOT NULL, value json NOT NULL, CONSTRAINT pk_param_id PRIMARY KEY ( id ), CONSTRAINT uk_param_name UNIQUE ( name ) ); INSERT INTO param ( name, value ) VALUES ( 'connection', '{ "username" : "Administrator", "login" : "root", "databases" : [ "db0", "db1" ], "enable" : { "day" : 0, "night" : 1 } }'::json ), ( 'link', '{ "from" : "db0", "to" : "db1" }'::json ); --   () SELECT value ->> 'username' FROM param WHERE name = 'connection'; --  Administrator --   (  ) () SELECT value #>> '{databases,0}' FROM param WHERE name = 'connection'; --  db0 --   SETOF ( key, value )   text () SELECT json_each_text ( value ) FROM param; --  (username,Administrator) (login,root) (databases,"[ ""db0"", ""db1"" ]") (enable,"{ ""day"" : 0, ""night"" : 1 }") (from,db0) (to,db1) --   () SELECT json_object_keys ( value ) FROM param; --  username login databases enable from to --     () SELECT * FROM json_populate_record ( null::t_link, ( SELECT value FROM param WHERE name = 'link' ) ); --  db0;db1 --   () SELECT json_array_elements ( value -> 'databases' ) FROM param; --  "db0" "db1" 

संक्षेप में, मैं कहना चाहता हूं कि मैं PostgreSQL के विकास से प्रसन्न हूं, परियोजना विकसित हो रही है, हालांकि अभी भी कच्ची चीजें हैं।

PS यदि आप अंत तक पढ़ते हैं तो धन्यवाद।

संदर्भ:

• PostgreSQL 9.3 में नया क्या है
• * से * चयन करें
• आधिकारिक PostgreSQL प्रलेखन