Представления и система правил
Эта страница переведена при помощи нейросети GigaChat.
Представления в PostgreSQL реализованы с использованием системы правил. Представление -- это по сути пустая таблица (не имеющая реального хранилища данных) с правилом ON SELECT DO INSTEAD. Обычно это правило называется _RETURN. Таким образом, представление вроде
CREATE VIEW myview AS SELECT * FROM mytab;
почти идентично конструкции
CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
хотя нельзя написать именно так, потому что таблицы не могут иметь правила ON SELECT.
У представления также могут быть другие виды правил DO INSTEAD, позволяющие выполнять команды INSERT, UPDATE или DELETE над представлением несмотря на отсутствие у него базового хранилища. Это обсуждается далее ниже, в разделе Раздел 39.2.4.
Как работают правила SELECT
Правила ON SELECT применяются ко всем запросам как последний шаг, даже если команда, которую дали, является INSERT, UPDATE или DELETE. Эти правила отличаются от правил других видов тем, что они модифицируют непосредственно дерево запросов, а не создают новое. Поэтому сначала описываются правила SELECT.
В настоящее время в правиле ON SELECT может быть только одно действие, и оно должно быть безусловным действием SELECT, которое является INSTEAD. Это ограничение было необходимо для того, чтобы сделать правила достаточно безопасными для открытия их обычным пользователям, и это ограничивает правила ON SELECT действовать так же, как представления.
Примеры для этой главы - два объединенных представления, которые выполняют некоторые вычисления, а также несколько дополнительных представлений, использующих их в свою очередь. Одно из двух первых представлений позже настраивается путем добавления правил для операций INSERT, UPDATE и DELETE, так что окончательным результатом будет представление, которое ведет себя как настоящая таблица с некоторой магической функциональностью. Это не такой простой пример для начала, и это затрудняет понимание. Но лучше иметь один пример, который охватывает все обсуждаемые моменты пошагово, чем иметь много разных примеров, которые могут запутаться в голове.
Настоящие таблицы, которые нужны в первых двух описаниях системы правил, следующие:
CREATE TABLE shoe_data (
shoename text, -- первичный ключ
sh_avail integer, -- число имеющихся пар
slcolor text, -- предпочитаемый цвет шнурков
slminlen real, -- минимальная длина шнурков
slmaxlen real, -- максимальная длина шнурков
slunit text -- единица длины
);
CREATE TABLE shoelace_data (
sl_name text, -- первичный ключ
sl_avail integer, -- число имеющихся пар
sl_color text, -- цвет шнурков
sl_len real, -- длина шнурков
sl_unit text -- единица длины
);
CREATE TABLE unit (
un_name text, -- первичный ключ
un_fact real -- коэффициент для перевода в см
);
Как видно, они представляют данные обувного магазина.
Представления создаются следующим образом:
CREATE VIEW shoe AS
SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name;
CREATE VIEW shoelace AS
SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
CREATE VIEW shoe_ready AS
SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
least(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
Команда для представления CREATE VIEW (которая является самой простой из тех, что есть) создаст отношение shoelace и запись в pg_rewrite, которая указывает, что существует правило переписывания, которое должно применяться всякий раз, когда отношение shoelace упоминается в таблице диапазона запроса. У этого правила нет квалификации правил (обсуждается позже с неповрежденными правилами, поскольку правила SELECT в настоящее время не могут их иметь), и оно является INSTEAD. Обратите внимание, что квалификация правил отличается от квалификации запросов. Действие нашего правила имеет квалификацию запроса. Действием правила является одно дерево запросов, которое является копией оператора SELECT в команде создания представления.
Две дополнительные записи таблицы диапазона для NEW и OLD, которые можно увидеть в записи pg_rewrite, не представляют интереса для правил SELECT.
Теперь заполняем unit, shoe_data и shoelace_data и выполняем простой запрос к представлению:
INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * FROM shoelace;
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
sl1 | 5 | black | 80 | cm | 80
sl2 | 6 | black | 100 | cm | 100
sl7 | 7 | brown | 60 | cm | 60
sl3 | 0 | black | 35 | inch | 88.9
sl4 | 8 | black | 40 | inch | 101.6
sl8 | 1 | brown | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 0 | brown | 0.9 | m | 90
(8 rows)
Это самый простой SELECT, который можно сделать с представлениями, поэтому воспользуемся этой возможностью, чтобы объяснить основы правил представлений. SELECT * FROM shoelace был интерпретирован парсером и произвел дерево запросов:
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;
И это передается системе правил. Система правил проходит через таблицу диапазонов и проверяет, есть ли правила для любого отношения. При обработке записи таблицы диапазонов для shoelace (единственной до сих пор) она находит правило _RETURN с деревом запросов:
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace old, shoelace new,
shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
Чтобы развернуть представление, механизм перезаписи просто создает запись таблицы диапазона подзапроса, содержащую дерево запроса действия правила, и заменяет эту запись таблицы диапазонов оригинальной записью, которая ссылалась на представление. Полученное переписанное дерево запросов почти такое же, как если бы ввели:
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM (SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) shoelace;
Однако есть одно отличие: таблица диапазонов подзапроса имеет две дополнительные записи shoelace old и shoelace new. Эти записи не участвуют напрямую в запросе, поскольку они не указаны в дереве соединений или списке целей подзапроса. Переписыватель использует их для хранения информации о проверке привилегий доступа, которая изначально присутствовала в записи таблицы диапазонов, ссылающейся на представление. Таким образом, исполнитель все равно проверит, что у пользователя есть надлежащие привилегии для доступа к представлению, даже несмотря на то, что нет прямого использования представления в переписанном запросе.
Это было первое примененное правило. Система правил продолжит проверку оставшихся записей таблицы диапазонов в верхнем запросе (в этом примере больше нет), и она будет рекурсивно проверять записи таблицы диапазонов в добавленном подзапросе, чтобы увидеть, какие из них ссылаются на представления. Но он не расширит old или new – иначе была бы бесконечная рекурсия! В этом примере нет правил перезаписи для shoelace_data или unit, поэтому перезапись завершена, и вышеупомянутое является окончательным результатом, данным планировщику.
Теперь напишем запрос, который определит, для каких туфель, которые сейчас находятся в магазине, есть подходящие шнурки (цвет и длина), а общее количество точно совпадающих пар больше или равно двум:
SELECT * FROM shoe_ready WHERE total_avail >= 2;
shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
sh1 | 2 | sl1 | 5 | 2
sh3 | 4 | sl7 | 7 | 4
(2 rows)
Вывод парсера на этот раз - дерево запросов:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM shoe_ready shoe_ready
WHERE shoe_ready.total_avail >= 2;
Первое примененное правило будет тем, которое относится к представлению shoe_ready, и оно приводит к дереву запроса:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
least(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail >= 2;
Аналогично, правила для shoe и shoelace заменяются в диапазонную таблицу подзапроса, что приводит к трехуровневому конечному дереву запросов:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
least(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name) rsh,
(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail > 2;
Это может показаться неэффективным, но планировщик объединит это в одноуровневое дерево запросов путем «поднятия вверх» подзапросов, а затем он спланирует соединения так же, как если бы записали их вручную. Таким образом, сжатие дерева запросов является оптимизацией, о которой системе переписывания не нужно беспокоиться.
Просмотр правил в непустых утверждениях
Два аспекта дерева запросов не рассматриваются в приведенном выше описании правил представления. Это тип команды и отношение результата. В действительности, тип команды не нужен для правил представления, но отношение результата может повлиять на то, как работает переписыватель запроса, поскольку требуется особая осторожность, если отношение результата является представлением.
Есть лишь несколько различий между деревом запросов для SELECT и любым другим оператором. Очевидно, что у них разные типы команд, а для команды, отличной от SELECT, отношение результата указывает на запись таблицы диапазонов, куда должен идти результат. Все остальное абсолютно одинаково. Итак, имея две таблицы t1 и t2 с колонками a и b, деревья запросов для двух операторов:
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;
UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;
Практически идентичны. В частности:
- Таблицы диапазонов содержат записи для таблиц
t1иt2. - Целевые списки содержат одну переменную, которая указывает на столбец
bзаписи таблицы диапазона для таблицыt2. - Выражения квалификации сравнивают столбцы
aобеих записей таблицы диапазона на равенство. - Деревья соединений показывают простое соединение между
t1иt2.
Следствием этого является то, что оба дерева запросов приводят к аналогичным планам выполнения: они оба являются объединениями двух таблиц. Для UPDATE отсутствующие столбцы из t1 добавляются в список целей планировщиком, и окончательное дерево запроса будет выглядеть так:
UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;
Таким образом, исполнитель, работающий над объединением, произведет точно такой же набор результатов, как:
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
Но есть небольшая проблема в UPDATE: часть плана исполнителя, которая выполняет объединение, не заботится о том, для чего предназначены результаты объединения. Он просто производит набор строк результата. Тот факт, что один из них - это команда SELECT, а другой - UPDATE, обрабатывается выше в исполнителе, где он знает, что это UPDATE, и он знает, что этот результат должен быть помещен в таблицу t1. Но какая из этих строк должна быть заменена новой строкой?
Чтобы решить эту проблему, еще одна запись добавляется в список целей в UPDATE (а также в операторах DELETE): текущий идентификатор кортежа (CTID). Это системный столбец, содержащий номер блока файла и позицию в блоке для строки. Зная таблицу, можно использовать CTID для получения исходной строки t1 для обновления. После добавления CTID в список целей запрос фактически выглядит следующим образом:
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
Теперь другая деталь PostgreSQL выходит на сцену. Старые строки таблицы не перезаписываются, и именно поэтому ROLLBACK работает быстро. В UPDATE, новая результирующая строка вставляется в таблицу (после удаления CTID) и в заголовке строки старой строки, на которую ссылался CTID, записи cmax и xmax устанавливаются на счетчике текущих команд и текущем идентификаторе транзакции. Таким образом, старая строка скрывается, и после фиксации транзакции уборщик вакуума может в конечном итоге удалить мертвую строку.
Зная все это, можно просто применить правила представления абсолютно таким же образом к любой команде. Нет никакой разницы.
Преимущества представлений в PostgreSQL
Выше показано, как система правил включает определения представлений в исходное дерево запросов. Во втором примере простой SELECT из одного представления создал конечное дерево запросов, которое является объединением четырех таблиц (unit использовался дважды с разными именами).
Преимущество реализации представлений с помощью системы правил заключается в том, что планировщик имеет всю информацию о том, какие таблицы должны быть отсканированы плюс отношения между этими таблицами плюс ограничительные условия из представлений плюс квалификации из исходного запроса в одном единственном дереве запросов. И это все еще ситуация, когда исходный запрос уже является объединением представлений. Планировщику необходимо решить, какой путь выполнения запроса является наилучшим, и чем больше информации у планировщика, тем лучше может быть принято это решение. А система правил, реализованная в PostgreSQL, обеспечивает доступность всей этой информации о запросе до этого момента.
Обновление представления
Что происходит, если представление указано в качестве целевой связи для INSERT, UPDATE, DELETE или MERGE? Выполнение описанных выше подстановок дало бы дерево запросов, в котором результирующая связь указывала бы на запись диапазона подзапроса, которая не будет работать. Существует несколько способов, с помощью которых PostgreSQL может поддерживать видимость обновления представления. В порядке сложности восприятия пользователем они следующие: автоматическая замена базовой таблицы вместо представления, выполнение триггера, определенного пользователем, или переписывание запроса в соответствии с правилом, определенным пользователем. Эти варианты обсуждаются ниже.
Если подзапрос выбирает из одной базовой связи и достаточно прост, переписыватель может автоматически заменить подзапрос на базовую связь таким образом, чтобы INSERT, UPDATE, DELETE или MERGE применялись к базовой связи соответствующим образом. Представления, которые являются «достаточно простыми» для этого, называются автоматически обновляемыми. Для получения подробной информации о типах представлений, которые могут быть автоматически обновлены, см. CREATE VIEW.
В качестве альтернативы операция может обрабатываться триггером INSTEAD OF, предоставляемым пользователем для представления (см. CREATE TRIGGER). Переписывание работает немного иначе в этом случае. Для INSERT переписыватель вообще ничего не делает с представлением, оставляя его в результате отношения для запроса. Для UPDATE, DELETE или MERGE все еще необходимо развернуть запрос представления для создания «старых» строк, которые команда попытается обновить, удалить или объединить. Таким образом, представление разворачивается нормально, но другой неразвернутый элемент диапазона таблиц добавляется в запрос для представления представления в его способности служить отношением результатов.
Проблема, которая теперь возникает, заключается в том, как идентифицировать строки, которые должны быть обновлены в представлении. Вспомним, что когда отношение результата является таблицей, специальный элемент CTID добавляется в целевой список для идентификации физических местоположений строк, которые будут обновляться. Это не работает, если отношение результата представляет собой представление, потому что представление не имеет никакого CTID, поскольку его строки не имеют фактических физических расположений. Вместо этого для операции UPDATE, DELETE или MERGE в целевой список добавляется специальная запись wholerow, которая расширяется для включения всех столбцов из представления. Исполнитель использует это значение для предоставления «старой» строки триггеру INSTEAD OF. Задача триггера - выяснить, что нужно обновить на основе старых и новых значений строк.
Еще одна возможность заключается в том, что пользователь может определить правила, которые задают альтернативные действия для команд INSERT, UPDATE, DELETE и на представлении. Эти правила будут переписывать команду, обычно преобразуя ее в команду, которая обновляет одну или несколько таблиц, а не представления. Обратите внимание, что это не работает с командой MERGE, которая сейчас не поддерживает правила для целевого отношения, кроме правил SELECT.
Обратите внимание, что правила оцениваются первыми, переписывая исходный запрос перед его планированием и выполнением. Поэтому, если представление имеет триггеры, а также правила для INSERT, UPDATE или DELETE, то сначала будут оценены правила, и в зависимости от результата триггеры могут вообще не использоваться.
Автоматическая переработка запроса INSERT, UPDATE, DELETE или MERGE или на простом представлении всегда пробуется последней. Поэтому, если у представления есть правила или триггеры, они заменят стандартное поведение автоматически обновляемых представлений.
Если для представления нет правил или триггеров, и переписыватель не может автоматически переписать запрос как обновление базовой основной связи, будет выброшена ошибка, потому что исполнитель не может обновить представление таким образом.