Geekly Articles each Day
📜 ⬆️ ⬇️

PostgreSQL 9.3 What's New?


Hello, habracheloveki! Not so long ago, PostgreSQL 9.3 was released and I would like to acquaint you with the most important client-related innovations that may be useful to you. This article covers the following:


Materialized views



A materialized view is a physical database object containing the results of some query. Undoubtedly, one of the most anticipated innovations. Let's see how to work with it in PostgreSQL .

Create a reference book of authors and a reference book with a link to the author:
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 ) );


Fill these tables with data - add a couple of authors and generate many, many books:
 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, ' ' );

')
For comparison, let's create a normal and materialized view (note that it takes a little longer to create the latter to sample and record the result):
 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;


Now, let's take a look at the query plan with the condition for the usual and materialized view:
 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


The data for the materialized view is heaped and does not have to be collected from different tables. But this is not all, since it is possible for them to create indexes. Improving the result:
 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


Not bad, the search is conducted on the index and the search time is significantly reduced.

But there is a nuance when using materialized views - after DML operations on the tables that make up the view, the view has to be updated:
 INSERT INTO book ( author_id, name ) VALUES ( 2, '  ' ); REFRESH MATERIALIZED VIEW mvw_book;


This can be automated with a trigger:
 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 ( );


Although the functional simulating materialized views could be done in PostgreSQL 9.2 (creating a table, indexes to it and a trigger that would perform a tricky query), but in general this is a convenient innovation.

Updateable views



DML operations can be applied to updated views. The truth is that the requirements for such representations are high: only one entity (table, view) in the FROM list, without the WITH , DISTINCT , GROUP BY , HAVING , LIMIT and OFFSET statements , without operations on sets ( UNION , INTERSECT and EXCEPT ) and to the fields not no functions or operations should be applied.

Refreshable views in action:
 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;


Please note that INSERT can be done in the view anyway, and UPDATE and DELETE can be done only when the set from the base table falls under the condition in the view:
 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


More advanced things can be done using rules for views.

Event Triggers



Also quite an expected innovation. Allow to intercept DDL commands in the database. They differ from ordinary triggers primarily because they are global, without reference to a specific table, but you can specify which commands to respond to.

They are created as follows:
 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 ( );


We carry out different DDL manipulations with the table:
 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;


The output should be:
 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


Only information about the event ( TG_EVENT ) and, in fact, about the team ( TG_TAG ) is available through plpgsql, but I hope it will be better in the future.

Recursive views



Allow to simplify the construction WITH RECURSIVE , if you want to build a representation on it.

Create a table and fill it with test data:
 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' );


Request through WITH RECURSIVE and similar to it, through recursive representation:
 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;


In fact, a recursive view is a wrapper over WITH RECURSIVE , as can be seen by looking at the text of the expanded view:
 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;


Lateral attachment



It allows you to access entities from an external query from a subquery. Example of use (counting the number of fields only for entities from the public schema):

 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;


Variable external tables



A new postgres_fdw module that allows read / write access to data located in another database. Previously, such functionality was in dblink , but in postgres_fdw everything is more transparent, standardized syntax and you can get better performance. Let's see how you can use postgres_fdw .

Create a new fdb database and a test table in it (it will be external to the current database):
 CREATE TABLE city ( country text NOT NULL, name text NOT NULL, CONSTRAINT uk_city_name UNIQUE ( country, name ) );


Let's go back to the current database and configure the external data source:
 --   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' );


Now we can work with the external table:
 --   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;


In order to make sure that the data actually got to the right place, switch to the fdb database and check:
 SELECT * FROM city;


Functions and operators for working with JSON type



The JSON type appeared in PostgreSQL 9.2, but there were only two functions — array_to_json (converting an array to JSON ) and row_to_json (converting a record to JSON ). Now there are more functions and you can work with this type quite well:
 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"


To summarize, I want to say that I am pleased with the development of PostgreSQL , the project is developing, although there are still raw things.

PS Thank you, if you have read to the end.

References:

Source: https://habr.com/ru/post/195898/

More articles:


All Articles