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

Начиная с версии 10, PostgreSQL поддерживает концепцию правил перезаписи, которые позволяют изменять способ выполнения запросов.

Фактически правила перезаписи — это то, как представления реализованы в PostgreSQL. Когда вы обращаетесь к представлению, ваш запрос фактически переписывается в соответствии с правилом, которое было создано при создании представления. По сути, представление — это, как мы сейчас увидим, правило перезаписи.

Однако не стоит расслабляться — некоторые условности всё же есть. Например, эти правила перезаписи сильно отличаются от плагинов перезаписи MySQL и в целом являются более комплексными.

Основные положения

Когда вы определяете правило перезаписи, вы сообщаете PostgreSQL, как обрабатывать определённый тип запроса к определённой таблице или представлению.

Правила перезаписи могут быть определены для SELECT, UPDATE, INSERT и DELETE. Они не существуют для любых других запросов, таких как DDL, SET, FETCH и т. д., по причинам, которые быстро станут очевидными.

Правила SELECT

Правила перезаписи для SELECT сильно ограничены; по сути, они могут определять только представление. На самом деле правила перезаписи для SELECT редко создаются напрямую; с тем же успехом вы можете определить представление, поскольку оператор CREATE VIEW приведёт к тому же результату, а именно к созданию новой строки в таблице pg_catalog.pg_rewrite.

Для этих ограничений есть очень веская причина: PostgreSQL включает правила в свою интерпретацию запросов. В случае правил SELECT он заменит имя отношения на то, что указано в правиле. Это означает, что правило SELECT должно быть простым SELECT, и поэтому правило не может делать ничего такого, что не может делать обычное представление.

Рассмотрим пример. Возьмём простую таблицу:

CREATE TABLE Persons (
  id INT PRIMARY KEY,
  first_name VARCHAR(200) NOT NULL,
  last_name VARCHAR(200) NOT NULL,
  dob DATE
);

INSERT INTO Persons VALUES
(1, 'Amy', 'Adams', '1974/08/20'),
(2, 'Brigitte', 'Bardot', '1934/09/28');

Теперь предположим, что мы хотим получить имя и фамилию в объединённом виде. Мы можем создать представление:

CREATE VIEW Persons_v AS
SELECT id, CONCAT(first_name, ' ', last_name) AS name, dob
FROM Persons

Посмотрим, что у нас получилось:



Это, конечно, самый обычный способ создания представления. Но мы можем сделать то же самое, создав таблицу и правило SELECT. Таблица будет превращена в представление путём добавления правила:

CREATE TABLE Persons_t (
  id INT,
  name TEXT,
  dob DATE
);

CREATE RULE "_RETURN" AS
ON SELECT TO Persons_t DO INSTEAD
SELECT id, CONCAT(first_name, ' ', last_name) AS name, dob
FROM Persons;

Несколько моментов, на которые следует обратить внимание:

• В таблице Persons_t не определён первичный ключ. Если бы он был, мы не смогли бы создать для него правило SELECT.
• Столбец name в Persons_t имеет тип TEXT, потому что это тип, возвращаемый функцией CONCAT.
• Правило называется _RETURN, потому что все правила SELECT должны иметь такое имя.

С этой «таблицей» Persons_t и установленным правилом перезаписи у нас теперь есть представление, хотя мы никогда напрямую не вызывали CREATE VIEW. Оно отразится, например, в pg_catalog.pg_views.

Вы почти наверняка никогда не будете этого делать; практически нет причин делать это таким образом, кроме как для удовлетворения собственного любопытства. А вот другие виды правил перезаписи намного интереснее.

Правила UPDATE, INSERT, и DELETE

Мы уже узнали, что правила SELECT фактически ограничены тем, что могут делать представления.

Однако для всех остальных правил перезаписи мы получаем гораздо больше свободы, поскольку они предназначены для определения того, как должны обрабатываться обновления, часто (но не только) в представлениях.

Допустим, мы хотим добавить значения в наше представление Persons_v. Если мы попробуем очевидное:

INSERT INTO Persons_v (id, name, dob)
VALUES (3, 'Charlie Chaplin', '1889/04/16')

Тогда мы получим ошибку:

[Code: 0, SQL State: 0A000]  ERROR: cannot insert into column "name" of view "persons_v"
  Detail: View columns that are not columns of their base relation are not updatable.

В этом есть смысл. PostgreSQL никак не может понять, как обращаться со столбцом name, который выводится с помощью формулы.

Вот тут-то и приходит на помощь правило INSERT:

CREATE RULE PersonsInsert AS
ON INSERT TO Persons_v DO INSTEAD
INSERT INTO Persons (id, first_name, last_name, dob)
VALUES (NEW.id, SPLIT_PART(NEW.name, ' ', 1), SPLIT_PART(NEW.name, ' ', 2), NEW.dob)

Теперь мы можем добавить значения в представление Persons_v, и значения для столбцов first_name и last_name будут обработаны правильно:

INSERT INTO Persons_v (id, name, dob)
VALUES (3, 'Charlie Chaplin', '1889/04/16')

Вот что получилось:

Попробуем заморочиться

Важно отметить, что правила могут включать несколько команд (за исключением правил SELECT). Это необходимо, если мы хотим поддерживать представления, охватывающие несколько таблиц.

Давайте создадим ещё одну таблицу:

CREATE TABLE Addresses (
  id INT PRIMARY KEY,
  street varchar(200) NOT NULL,
  city varchar(100) NOT NULL,
  person_id int REFERENCES Persons ON DELETE CASCADE,
  end_date timestamp
);

INSERT INTO Addresses (id, street, city, person_id) VALUES
(100, '1428 Elm Street', 'Springwood', 1),
(101, '742 Evergreen Terrace', 'Springfield', 2),
(102, '221B Baker Street', 'London', 3);

Столбец end_date сыграет свою роль позже, поэтому пока не обращайте на него внимания.

Теперь мы создадим новое представление, охватывающее обе таблицы:

CREATE VIEW PersonAddress AS
SELECT p.id as pid,
        CONCAT(first_name, ' ', last_name) AS name,
        a.id as aid,
        CONCAT(street, ', ', city) as address
FROM Persons p
LEFT JOIN Addresses a
ON a.person_id = p.id
WHERE a.end_date IS NULL

Посмотрим, что у нас получилось:



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

Но мы можем создать правило, которое будет обрабатывать это правильно:

CREATE RULE PersonAddressInsert AS
ON INSERT TO PersonAddress DO INSTEAD
(
  INSERT INTO Persons (id, first_name, last_name)
    VALUES (NEW.pid, SPLIT_PART(NEW.name, ' ', 1), SPLIT_PART(NEW.name, ' ', 2));
  INSERT INTO Addresses (id, street, city, person_id)
    VALUES (NEW.aid, SPLIT_PART(NEW.address, ', ', 1), SPLIT_PART(NEW.address, ', ', 2), NEW.pid)
)

Затем мы можем вставить данные в обе таблицы одновременно с помощью представления:

INSERT INTO PersonAddress (pid, name, aid, address)
VALUES (4, 'Doris Day', 103, '42 Wallaby Way, Sydney')

Теперь у нас есть такое:

Двигаемся в сторону триггеров

Мы уже видели, что правила INSERT, UPDATE и DELETE могут содержать несколько утверждений, а это означает, мы можем подойти к делу творчески.

Например, что если мы не хотим обновлять адрес, а вместо этого пометим старый адрес как устаревший с помощью столбца end_date и вставим новый адрес?

Такие вещи обычно обрабатываются триггером, но если мы просматриваем представление для обновления, мы можем обработать это в правиле UPDATE:

CREATE RULE PersonAddressUpdate AS
ON UPDATE TO PersonAddress DO INSTEAD
(
  UPDATE Persons
    SET first_name=SPLIT_PART(NEW.name, ' ', 1), last_name=SPLIT_PART(NEW.name, ' ', 2)
    WHERE id=NEW.pid;
  INSERT INTO Addresses (id, street, city, person_id)
    VALUES ((SELECT MAX(id) + 1 FROM Addresses), SPLIT_PART(NEW.address, ', ', 1),
      SPLIT_PART(NEW.address, ', ', 2), NEW.pid);
  UPDATE Addresses SET end_date = NOW()
    WHERE id=NEW.aid;
)

Пуристы SQL могут немного прищуриться, но это будет работать. Теперь, когда мы выполним обновление представления, мы получим новую строку в таблице Addresses, старая строка будет иметь значение в столбце end_date, а имя человека будет обновлено:

UPDATE PersonAddress
SET name = 'Doris Kappelhoff', address = '32 Spooner Street, Quahog'
WHERE aid=103

Добавляем в таблицы параметры поведения

Вы, возможно, заметили DO INSTEAD, когда мы определяли правило. Существует также DO ALSO, который позволяет нам добавить поведение к обычному выполнению запроса.

По большому счёту правила не могут быть рекурсивными. Вы не можете иметь правило INSERT для таблицы T, которое включает INSERT для таблицы T — это просто перейдёт в бесконечную рекурсию.

Но можно изменить (скажем) UPDATE в таблице на (скажем) DELETE. Правила существуют не только для представлений.

Например, мы можем захотеть иметь механизм «сбора мусора», с функционалом автоматического удаления любого человека, у которого больше нет адреса. Это можно сделать с помощью правила, определённого для таблицы Addresses:

CREATE RULE AddressDelete AS
ON DELETE TO Addresses DO ALSO
DELETE FROM Persons
WHERE id = OLD.person_id AND
  (SELECT COUNT(*) from Addresses WHERE person_id = OLD.person_id) = 1

Теперь, если мы удалим последний адрес человека, то автоматически удалим человека, которому принадлежал этот адрес (в данном случае Чарли Чаплина):

DELETE FROM Addresses WHERE id=102

Можно определить несколько правил одного типа для одной таблицы; в этом случае они будут выполняться в алфавитном порядке.

Параметры разрешений

Поскольку правила могут затрагивать таблицы, не упомянутые в исходном запросе, возникает вопрос: какие разрешения должна использовать база данных при выполнении этих правил?

Так, в нашем последнем примере мы удалили адрес, что предполагает, имеющиеся у нас для этого соответствующие привилегии в таблице Addresses.

Но, возможно, неизвестно для нас, это удаление также удалило человека. Что, если у нас нет прав DELETE на таблицу Persons?

В PostgreSQL все правила принадлежат владельцу таблицы, к которой эти правила привязаны. Когда правило выполняется, разрешения владельца этой таблицы будут применяться при обращении к таблицам, которые не были непосредственно упомянуты в исходном запросе.

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

Верный ли это путь для выполнения подобных задач?

Правила перезаписи полезны, даже незаменимы, когда вы хотите работать с представлениями, и вам нужно определить, что происходит с INSERT, UPDATE и DELETE. Надеюсь, что хотя бы из-за этой информации моё руководство было для вас полезным.

Правила перезаписи также могут быть полезны, если вам нужно изменить поведение некоторых таблиц, в пределах разумного.

Но как далеко вы должны идти в направлении правил перезаписи? То, что вы можете что-то сделать, не означает, что вы должны это делать.

Это непростой вопрос. Триггеры могут иметь больше смысла, поскольку их цель часто более ясна, и у них больше возможностей (BEFORE/AFTER/INSTEAD OF, FOR EACH ROW и т. д.).

Но правила перезаписи иногда проще выразить, чем триггеры. В документации PostgreSQL есть обсуждение этой темы для тех, кто хочет в неё погрузиться.

Моя общая рекомендация — использовать правила перезаписи только для того, чтобы делать нетривиальные представления обновляемыми, и использовать триггеры для всех других целей. Правила перезаписи менее знакомы большинству людей, и они совершенно уникальны для PostgreSQL. Имейте в виду, следующий человек, который будет поддерживать то, что вы делаете, может быть не таким опытным, как вы.

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

---

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

— 15% на все тарифы VDS (кроме тарифа Прогрев) — HABRFIRSTVDS.