PostgreSQL 9.5: що нового? Частина 1

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

INSERT… ON DO CONFLICT 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_actionDO 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 INSERT BEFORE 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

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.