PostgreSQL 9.3 Что нового?

Здравствуйте, хабрачеловеки! Не так уж давно вышел релиз 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

Неплохо, поиск ведётся по индексу и время поиска существенно сократилось.

Но есть и нюанс при использовании материализированных представлений — после DML операций над таблицами, из которых состоит представление, представление приходится обновлять:

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 ( );

Хотя функционал, симулирующий материализированные представления, можно было сделать и в PostgreSQL 9.2 (создав таблицу, индексы к ней и триггера, которые бы выполняли хитрый запрос), но в целом это удобное нововведение.

Обновляемые представления

К обновляемым представлениями можно применять DML-операции. Правда вот требования к таким представлениям высокие: только одна сущность (таблица, представление) в списке FROM, без операторов WITH, 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

Более продвинутые вещи можно делать, используя правила к представлениям.

Триггеры к событиям

Тоже, довольно ожидаемое нововведение. Позволяют перехватывать 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 ( );

Проводим разные DDL-манипуляции с таблицей:

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), но, надеюсь, в будущем будет лучше.

Рекурсивные представления

Позволяют упростить конструкцию WITH 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 и аналогичный ему, через рекурсивное представление:

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;

На самом деле, рекурсивное представление — это обёртка над WITH 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;

Латеральное присоединение

Позволяет обращатся из подзапроса к сущностями из внешнего запроса. Пример использования (подсчет количества полей только для сущностей из схемы public):

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, позволяющий получить read/write доступ к данным, расположенным в другой БД. Ранее такая функциональность была в 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, проект развивается, хоть и есть еще сырые вещи.

P.S. Спасибо, если дочитали до конца.

Ссылки:

• What's new in PostgreSQL 9.3
• select * from depesz
• Официальная документация по PostgreSQL