[Из песочницы] PostgreSQL 9.5: что нового? Часть 1

В третьем квартале 2015 ожидается релиз PostgreSQL 9.5. Как всегда, новая версия кроме новых багов приносит новые фичи и «плюшки». В данной статье будут рассмотрены две из них, а именно INSERT… ON CONFLICT DO NOTHING/UPDATE и Row-level security. Уже вышла первая альфа-версия, поэтому самые нетерпеливые могут её установить и попробовать новый функционал.

INSERT… ON CONFLICT DO NOTHING/UPDATE

Он же в просторечии UPSERT. Позволяет в случае возникновения конфликта при вставке произвести обновление полей или же проигнорировать ошибку.

То, что раньше предлагалось реализовывать с помощью хранимой функции, теперь будет доступно из коробки. В выражении INSERT можно использовать условие ON CONFLICT DO NOTHING/UPDATE. При этом в выражении указывается отдельно conflict_target (по какому полю/условию будет рассматриваться конфликт) и conflict_action (что делать, когда конфликт произошел: DO NOTHING или DO UPDATE SET).

Полный синтаксис выражения INSERT будет такой:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

Для нас самое интересное начинается после ON CONFLICT.

Давайте посмотрим на примерах. Создадим таблицу, в которой будут лежать учетные данные неких персон:

CREATE TABLE account
(
  id bigserial,
  name varchar,
  surname varchar,
  address varchar,
  PRIMARY KEY (id),
  CONSTRAINT unique_person UNIQUE (name, surname, address)
);
Query returned successfully with no result in 31 ms.

Выполним запрос на вставку

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO NOTHING;
Query returned successfully: one row affected, 12 ms execution time.

SELECT * FROM ACCOUNT;

id	name	surname	address
1	Вася	Пупкин	Москва, Кремль

Здесь conflict_target — это (id), а conflict_action — DO NOTHING.
Если попытаться выполнить этот запрос второй раз, то вставки не произойдет, при этом и не выдаст никакого сообщения об ошибке:

Query returned successfully: 0 rows affected, 12 ms execution time.

Если бы мы не указали ON CONFLICT (id) DO NOTHING, то получили бы ошибку:

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль');

********** Error **********

ERROR: duplicate key value violates unique constraint "account_pkey"
SQL state: 23505
Detail: Key (id)=(1) already exists.

Такое же поведение (как и у ON CONFLICT (id) DO NOTHING) будет у запроса:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (name, surname, address) DO NOTHING;

Query returned successfully: 0 rows affected, 12 ms execution time.

В нем мы уже берем значение id по умолчанию (из последовательности), но указываем другой conflict_target — по трем полям, на которые наложено ограничение уникальности.

Как упоминалось выше, также можно указать conflict_target с помощью конструкции ON CONSTRAINT, указав непосредственно имя ограничения:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT ON CONSTRAINT unique_person DO NOTHING;
Query returned successfully: 0 rows affected, 11 ms execution time.

Особенно полезно это в случае, если у вас есть исключающее ограничение (exclusion constraint), к которому вы можете обратиться только по имени, а не по набору колонок, как в случае с ограничением уникальности.

Если у вас построен частичный уникальный индекс, то это также можно указать в условии. Пусть в нашей таблице уникальными сочетания фамилия+адрес будут только у людей с именем Вася:

ALTER TABLE account DROP CONSTRAINT unique_person; 
CREATE UNIQUE INDEX unique_vasya ON account (surname, address) WHERE name='Вася';

Тогда мы можем написать такой запрос:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (surname, address) WHERE name='Вася' DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

Ну и, наконец, если вы хотите, чтобы DO NOTHING срабатывал при любом конфликте уникальности/исключения при вставке, то это можно записать следующим образом:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

Стоит заметить, что задать несколько conflict_action невозможно, поэтому если указан один из них, а сработает другой, то будет ошибка при вставке:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO NOTHING;

********** Error **********

ERROR: duplicate key value violates unique constraint "unique_person"
SQL state: 23505
Detail: Key (name, surname, address)=(Вася, Пупкин, Москва, Кремль) already exists.

Перейдем к возможностям DO UPDATE SET.

Для DO UPDATE SET в отличие от DO NOTHING указание conflict_action обязательно.

Конструкция DO UPDATE SET обновляет поля, которые в ней указаны. Значения этих полей могут быть заданы явно, заданы по умолчанию, получены из подзапроса или браться из специального выражения EXCLUDED, из которого можно взять данные, которые изначально были предложены для вставки.

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Петя', 'Петров', 'Москва, Кремль')
ON CONFLICT (id)
DO UPDATE SET
name='Петя',
surname='Петров';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

id	name	surname	address
1	Петя	Петров	Москва, Кремль

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Петя', 'Петров', 'Москва, Кремль')
ON CONFLICT (id)
DO UPDATE SET
name=EXCLUDED.name || ' (бывший ' || a.name || ')',
surname=EXCLUDED.surname || ' (бывший ' || a.surname || ')';
Query returned successfully: one row affected, 13 ms execution time.

SELECT * FROM ACCOUNT;

id	name	surname	address
1	Петя (бывший Вася)	Петров (бывший Пупкин)	Москва, Кремль

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=DEFAULT,
surname=DEFAULT;
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

id	name	surname	address
1	NULL	NULL	Москва, Кремль

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=(SELECT some_field FROM other_table LIMIT 1);

Также может быть использовано условие WHERE. Например, мы хотим, чтобы поле name не обновлялось, если в поле address в строке таблицы уже содержится текст «Кремль», в противном же случае — обнловлялось:

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=EXCLUDED.name
WHERE a.name not like '%Кремль%';

Query returned successfully: 0 rows affected, 12 ms execution time.

А если хотим, чтобы поле name не обновлялось, если в поле address во вставляемых данных содержится текст «Кремль», в противном же случае — обнловлялось:

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Красная площадь')
ON CONFLICT (id) DO UPDATE SET
name=EXCLUDED.name
WHERE EXCLUDED.name not like '%Кремль%';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT

id	name	surname	address
1	Вася	NULL	Москва, Кремль

ROW LEVEL SECURITY

Row-level security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.

Данная функциональность может быть интересна тем, кто использует базы с большим числом пользователей.

Работает это следующим образом: описываются правила для конкретной таблицы, согласно которым ограничивается доступ к конкретным строкам при выполнении определнных команд, с помощью выражения CREATE POLICY. Каждое правило содержит некое логическое выражение, которое должно быть истинным, чтобы строка была видна в запросе. Затем правила активируются с помощью выражения ALTER TABLE… ENABLE ROW LEVEL SECURITY. Затем при попытке доступа, например при запросе SELECT, проверяется, имеет ли пользователь право на доступ к конкретной строке и если нет, то они ему не показываются. Суперпользователь по умолчанию может видеть все строки, так как у него по умолчанию выставлен флаг BYPASSRLS, который означает, что для данной роли проверки осуществляться не будут.

Синтаксис выражения CREATE POLICY такой:

CREATE POLICY name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

Правила создаются для конкретных таблиц, поэтому в БД может быть несколько правил с одним и тем же именем для различных таблиц.
После выражения FOR указывается, для каких именно запросов применяется правило, по умолчанию — ALL, то есть для всех запросов.

После TO — для каких ролей, по умолчанию — PUBLIC, то есть для всех ролей.

Далее, в выражении USING указывается булевское выражение, которое должно быть true, чтобы конкретная строка была видна пользователю в запросах, которые используют уже имеющиеся данные (SELECT, UPDATE, DELETE). Если булевское выражение вернуло null или false, то строка видна не будет.

В выражении WITH CHECK указывается булевское выражение, которое должно быть true, чтобы запрос, добавляющий или изменяющий данные (INSERT или UPDATE), прошел успешно. В случае, если булевское выражение вернет null или false, то будет ошибка. Выражение WITH CHECK выполняется после триггеров BEFORE (если они присутствуют) и до любых других проверок. Поэтому, если триггер BEFORE модифицирует строку таким образом, что условие не вернет true, будет ошибка. Для успешного выполнения UPDATE необходимо, чтобы оба условия вернули true, в том числе, если в запросе INSERT… ON CONFILCT DO UPDATE произойдет конфликт и запрос попытается модифицировать данные. Если выражение WITH CHECK опущено, вместо него будет подставляться условие из выражения USING.
В условиях нельзя использовать аггрегирующие или оконные функции.

Обычно, требуется управлять доступом, исходя из того, какой пользователь БД запрашивает данные, поэтому нам пригодятся функции, возвращающие информацию о системе (System Information Functions).

Перейдем к примерам:

Добавим в таблицу account поле db_user, заполним это поле для уже существующей записи и добавим новые записи:

ALTER TABLE account ADD COLUMN db_user varchar;
Query returned successfully with no result in 16 ms.
UPDATE account SET db_user='pupkin' WHERE surname='Пупкин';
INSERT INTO account (name, surname, address, db_user)
VALUES ('Петр', 'Петров', 'Москва, Красная площадь', 'petrov'), 
('Иван', 'Сидоров', 'Санкт-Петербург, Зимний дворец', 'sidorov');
Query returned successfully: 2 rows affected, 31 ms execution time.

Создадим роли:

CREATE ROLE pupkin WITH LOGIN PASSWORD 'pupkin';
CREATE ROLE petrov WITH LOGIN PASSWORD 'petrov';
Query returned successfully with no result in 31 ms.

Создадим правило и включим RLS на таблице:

CREATE POLICY select_self ON account
FOR SELECT
USING (db_user=current_user);
ALTER TABLE account ENABLE ROW LEVEL SECURITY;
Query returned successfully with no result in 12 ms.

В данном запросе мы создали правило, согласно которому, пользователю в запросе SELECT будут видны только те строки, в которых значение поля db_user совпадает с именем текущего пользователя БД.

Выполним запрос от пользователя postgres:

SELECT * FROM account

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin
5	Петр	Петров	Москва, Красная площадь	petrov
6	Иван	Сидоров	Санкт-Петербург, Зимний дворец	sidorov

Выполним тот же запрос от пользователя pupkin:

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin

Создадим правило, по которому строки с фамилией «Пупкин» может вставлять только пользователь pupkin:

CREATE POLICY insert_update_pupkin ON account
WITH CHECK (surname<>'Пупкин' OR current_user='pupkin')

Попробуем выполнить запрос от пользователя pupkin:

INSERT INTO account (name, surname, address)
VALUES ('Дмитрий', 'Пупкин', 'Киев, Майдан')
Query returned successfully: one row affected, 13 ms execution time.

Проверим:

select * from account;

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin

Оп-па! Мы забыли указать поле db_user и запись, которую мы вставили, мы уже не увидим. Что ж, давайте исправим такую логику с помощью триггера, в котором будем заполнять поле db_user именем текущего пользователя:

CREATE OR REPLACE FUNCTION fill_db_user() RETURNS TRIGGER AS 
$BODY$
BEGIN
  NEW.db_user = current_user;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER fill_db_user BEFORE INSERT ON account
FOR EACH ROW EXECUTE PROCEDURE fill_db_user();

Пробуем снова:

INSERT INTO account (name, surname, address)
VALUES ('Иван', 'Пупкин', 'Киев, Майдан');
select * from account;</b>
<table border="1" style="border-collapse:collapse">
<tr><th>id</th><th>name</th><th>surname</th><th>address</th><th>db_user</th></tr>
<tr><td>1</td><td>Вася</td><td>Пупкин</td><td>Москва, Кремль</td><td>pupkin</td></tr>
<tr><td>21</td><td>Иван</td><td>Пупкин</td><td>Киев, Майдан</td><td>pupkin</td></tr></table>
Попробуем изменить данные о Иване Пупкине пользователем petrov:
<source lang="SQL">UPDATE account SET db_user='petrov'
WHERE id=21
Query returned successfully: 0 rows affected, 13 ms execution time.

Как видим, данные не изменились, это произошло потому, что условие USING из правила select_self не выполнилось.

Если одному запросу соответствует несколько правил, то они объединяются через OR.

Стоит отметить, что правила применяются только при явным запросам к таблицам и не применяются при проверках, которые осуществляет система (constaints, foreign keys и т.п.). Это означает, что пользователь с помощью запросов, определить, что какое-либо значение существует в БД. Например, если пользователь может осуществлять вставку в таблицу, которая ссылается на другую таблицу, из которой он не может делать SELECT. В таком случае, он может попытаться сделать INSERT в первую таблицу и по результату (произошла вставка или же произошла ошибка при проверке ссылочной целостности) определить, существует ли значение во второй таблице.

Вариантов использования row-level security можно придумать множество:

• одну и ту же базу используют несколько приложений с разным функционалом
• несколько инстансов одного и того же приложения с разными правами
• доступ по ролям или группам пользователей
• и т.д.

В следующей статье я планирую рассмотреть такие новые фичи PostgreSQL 9.5, как

• SKIP LOCKED
• BRIN-индексы
• GROUPING SETS, CUBE, ROLLUP
• Новые функции для JSONB
• IMPORT FOREIGN SCHEMA

This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.