PostgreSQL 9.3 What's new?
Hello, habrachelovek! Not so long ago there was a release of PostgreSQL 9.3 and I would like to present You with the most important innovations for the client part, which may be useful to You. In this article, we'll cover:
the
-
the
- materialiserades presentation the
- updatable views the
- triggers on events the
- text representation the
- lateral joining the
- are modified by external tables the
- functions and operators for working with type JSON
Materialiserades representation
Materialiserades representation of the 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 directory of authors and reference books, with a link to the author:
the
CREATE TABLE author
(
id serial NOT NULL,
first_name text NOT NULL,
last_name text NOT NULL,
Pk_author_id CONSTRAINT PRIMARY KEY ( id ),
Uk_author_name CONSTRAINT UNIQUE ( first_name, last_name )
);
CREATE TABLE book
(
id serial NOT NULL,
author_id integer NOT NULL,
name text NOT NULL,
Pk_book_id CONSTRAINT PRIMARY KEY ( id ),
Fk_book_author_id CONSTRAINT FOREIGN KEY ( author_id ) REFERENCES author ( id ),
Uk_book_name CONSTRAINT UNIQUE ( author_id, name )
);
Fill these tables with data — let's add a couple of authors and will generate lots and lots of books:
the
INSERT INTO author ( first_name, last_name ) VALUES ( 'Ivan', 'Ivanov' ); -- id generated = 1
INSERT INTO author ( first_name, last_name ) VALUES ( 'Peter', 'Smith' ); -- id generated = 2
INSERT INTO book ( author_id, name ) VALUES
( 1, 'a Treatise on the void (part' || generate_series ( 1, 100000 ) || ')' );
INSERT INTO book ( author_id, name ) VALUES
( 2, 'the Impossibility of being' ),
( 2, 'Happy ending' );
For comparison, create an ordinary and materialiserades performance (note that the latter requires a little more time to sample and record the result):
the
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 look at the query plan with the condition for ordinary and materialiserades of submission:
the
EXPLAIN ANALYZE SELECT * FROM vw_book WHERE author_name = 'Peter Smith';
--
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) = 'Peter Smith'::text)
Rows Removed by Filter: 1
Total runtime: 19.452 ms
EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = 'Peter Smith';
--
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 = 'Peter Smith'::text)
Rows Removed by Filter: 100000
Total runtime: 15.905 ms
Data for materialiserades notion was heap and they do not have to collect from different tables. But that's not all, as they have the ability to create indexes. Improve the result:
the
CREATE INDEX idx_book_name ON mvw_book ( author_name );
EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = 'Peter Smith';
--
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 = 'Peter Smith'::text)
Total runtime: 0.099 ms
Well, the search is performed at index and search time is significantly reduced.
But there is a caveat when using materialiserades ideas — after DML operations on tables that comprise the view, the view must be updated:
the
INSERT INTO book ( author_id, name ) VALUES
( 2, 'Lost in the mist' );
REFRESH MATERIALIZED VIEW mvw_book;
You can automate this with a trigger:
the
CREATE OR REPLACE FUNCTION mvw_book_refresh ( )
RETURNS trigger AS
$BODY$
BEGIN
REFRESH MATERIALIZED VIEW mvw_book;
RETURN NULL;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
Tr_book_refresh CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE
ON book FOR EACH STATEMENT EXECUTE PROCEDURE mvw_book_refresh ( );
Tr_author_refresh CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE
Although the functionality of simulating materialiserades submission could be done in PostgreSQL 9.2 (after creating the table, indexes thereto, and a trigger that would do sly's request), but overall it is an excellent innovation.
Updatable views
For updatable views, you can apply DMLoperations. It is true that the requirements for such submissions high: only one entity (table, view) in the list FROM, without the WITH, DISTINCT, GROUP BY, HAVING, LIMIT and OFFSET without the (UNION, INTERSECT and EXCEPT) and the fields do not apply any functions or operations.
Updatable views in action:
the
CREATE TABLE employee
(
id serial NOT NULL,
fullname text NOT NULL,
birthday date
salary numeric NOT NULL DEFAULT 0.0,
Pk_employee_id CONSTRAINT PRIMARY KEY ( id ),
Uk_employee_fullname CONSTRAINT UNIQUE ( fullname ),
Ch_employee_salary CONSTRAINT CHECK ( salary >= 0.0 )
);
INSERT INTO employee ( fullname, salary ) VALUES ( 'Ivan Ivanov', 800.0 );
INSERT INTO employee ( fullname, salary ) VALUES ( 'Peter Petrov', 2000.0 );
INSERT INTO employee ( fullname, salary ) VALUES ( 'Unknown', 1500.0 );
CREATE VIEW vw_employee_top_salary AS
SELECT employee.fullname AS name, employee.salary
FROM employee
WHERE employee.salary >= 1000.0;
-- use a view
INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( 'semen Sidorov', 2500.0 );
Vw_employee_top_salary UPDATE SET salary = 2200.0 WHERE name = 'Peter Smith';
DELETE FROM vw_employee_top_salary WHERE name = 'Unknown';
-- output the results
SELECT * FROM vw_employee_top_salary;
Please note that the INSERT in the submission can be done in any case and UPDATE and DELETE — only when set from the base table falls falls under the condition in the view:
the
INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( 'Anonymous', 0.0 ); -- added line
Vw_employee_top_salary UPDATE SET salary = 3000.0 WHERE name = 'Anonymous'; -- nothing will change as salary equal to 0.0
DELETE FROM vw_employee_top_salary WHERE name = 'Anonymous'; -- nothing will change as salary equal to 0.0
More advanced things can be done using rules to view.
Triggers on events
Also, quite expectedly innovation. Allow you to intercept the DDL command in the database. Different from the usual triggers in the first place so that they are global, without reference to a specific table, but you can specify which commands to respond.
Created:
the
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 ( );
Hold different DDL-manipulation of the table:
the
CREATE TABLE article
(
id SERIAL NOT NULL,
name text NOT NULL,
Pk_article_id CONSTRAINT PRIMARY KEY ( id ),
Uk_article_name CONSTRAINT 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:
the
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
Using plpgsql only available information about the event (TG_EVENT) and the command (TG_TAG), but hopefully in the future will be better.
Recursive representation
To simplify the design WITH RECURSIVE, if you want to build for her performance.
We will create a table and fill it with test data:
the
CREATE TABLE directory
(
id serial NOT NULL,
parent_id integer,
name text NOT NULL,
Pk_directory_id CONSTRAINT PRIMARY KEY ( id ),
Fk_directory_parent_id CONSTRAINT FOREIGN KEY ( parent_id ) REFERENCES directory ( id ),
Uk_directory_name CONSTRAINT UNIQUE ( parent_id, name )
);
INSERT INTO directory ( parent_id, name ) VALUES ( NULL, 'usr' ); -- id generated = 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 ( 4, 'opt' ); -- id generated = 5
INSERT INTO directory ( parent_id, name ) VALUES ( 5, 'tmp' );
INSERT INTO directory ( parent_id, name ) VALUES ( 4, 'log' ); -- id generated = 7
INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'samba' );
INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'news' );
Query using WITH RECURSIVE and similar to him, using a recursive representation:
the
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
Vw_directory INNER JOIN 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
Vw_directory INNER JOIN t ON d.parent_id = t.id;
SELECT * FROM vw_directory ORDER BY path;
In fact, the recursive view is a wrapper over WITH RECURSIVE, which you can see by viewing the text sformirovannogo of submission:
the
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
Vw_directory JOIN 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 accession
Allows calling out from the subquery to entity from the outer query. Usage example (counting the number of fields only for entities from the public schema):
the
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;
Modified in the external table
A new module, postgres_fdw, which allows to obtain read/write access to data located in another database. Previously, such functionality was in dblink, but postgres_fdw more transparent, standardized syntax and to get the best performance. View how you can use postgres_fdw.
Create a new database, fdb and in it the test table (it will be external to the current database)
the
CREATE TABLE city
(
country text NOT NULL,
name text NOT NULL,
Uk_city_name CONSTRAINT UNIQUE ( country, name )
);
Back to the current database and configure the external data source:
the
-- creating extensions
CREATE EXTENSION postgres_fdw;
-- add the external server
Fdb_server CREATE SERVER FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', dbname 'fdb' );
-- display the user
CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS ( password 'pwd' );
-- create the external table
CREATE FOREIGN TABLE fdb_city ( country text, name text ) SERVER fdb_server OPTIONS ( table_name 'city' );
Now we can work with the external table:
the
-- add a record
INSERT INTO fdb_city ( country, name ) VALUES ( 'USA', 'Las Vegas' );
-- change it
UPDATE fdb_city SET name = 'New Vegas' WHERE name = 'New Vegas';
-- look what happened
SELECT * FROM fdb_city;
To make sure the data really got there where it is necessary, switch to the database fdb and check:
the
SELECT * FROM city;
Functions and operators for working with JSON type
Type JSON appeared in PostgreSQL 9.2, but it was only two — array_to_json (conversion of an array in JSON) and row_to_json (conversion records in JSON). Now functions have become more and more possible to work with this type:
the
CREATE TYPE t_link AS
(
the "from" text,
the "to" text
);
CREATE TABLE param
(
id serial NOT NULL,
name text NOT NULL,
json value is NOT NULL
Pk_param_id CONSTRAINT PRIMARY KEY ( id ),
Uk_param_name CONSTRAINT 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 );
-- the field value (query)
SELECT value ->> 'username' FROM param WHERE name = 'connection';
- the result
Administrator
-- the field value (in path) (request)
SELECT value #>> '{"databases", 0}' FROM param WHERE name = 'connection';
- the result
db0
-- conversion to SETOF ( key, value ) type text (query)
SELECT json_each_text ( value ) FROM param;
- the result
(username,Administrator)
(login,root)
("databases"," [""db0"", ""db1"" ]")
(enable,"{ ""day"" : 0, "night" : 1 }")
("from", db0)
(to db1)
-- key values (query)
SELECT json_object_keys ( value ) FROM param;
- the result
username
login
databases
enable
from
to
-- value in the form of a record (request)
SELECT * FROM json_populate_record ( null::t_link, ( SELECT value FROM param WHERE name = 'link' ) );
- the result
db0;db1
-- the values of the array (query)
Json_array_elements SELECT ( value -> 'databases' ) FROM param;
- the result
"db0"
"db1"
Summing up, I want to say I'm glad the development of PostgreSQL, the project is developing, though there is still the raw stuff.
P. S. Thank you if you read to the end.
Links:
the
Комментарии
Отправить комментарий