Система правил
В этой главе рассматривается имеющаяся в QHB система правил. Промышленные системы правил принципиально просты, но их фактическое использование сопряжено со множеством тонких моментов.
В некоторых других СУБД определяются активные правила баз данных, в которых обычно хранятся процедуры и триггеры. В QHB их тоже можно реализовать с помощью функций и триггеров.
Система правил (точнее говоря, система правил перезаписи запросов) полностью отличается от хранимых процедур и триггеров. Она модифицирует запросы так, чтобы в них учитывались правила, а затем передает модифицированный запрос планировщику запросов для планирования и выполнения. Это очень действенный механизм, который можно применять для множества объектов, например, для процедур на языке запросов, представлений и версий. Теоретические основы и потенциал этой системы правил рассматриваются также в статьях «On Rules, Procedures, Caching and Views in Database Systems» и «An overview of the Sequoia 2000 project».
Дерево запроса
Чтобы понять, как работает система правил, нужно знать, когда она вызывается и что собой представляют ее входные данные и результаты.
Система правил располагается между синтаксическим анализатором и планировщиком. Она принимает вывод анализатора, одно дерево запроса и пользовательские правила перезаписи, которые тоже являются деревьями запросов с некоторой дополнительной информацией, и создает в качестве результата ноль и более деревьев запросов. Таким образом, на входе и выходе системы правил всегда находится то, что мог бы выдать и сам синтаксический анализатор, поэтому все, что она видит, по сути можно представить в виде оператора SQL.
Далее, что такое дерево запроса? Это внутреннее представление оператора SQL, в котором формирующие его части хранятся отдельно. Эти деревья запросов могут отображаться в журнале сервера, если установить конфигурационные параметры debug_print_parse, debug_print_rewritten или debug_print_plan. Действия правил тоже хранятся в виде деревьев запросов в системном каталоге pg_rewrite. Они не форматируются как вывод журнала, но содержат ровно ту же информацию.
Для прочтения исходных запросов дерева запроса требуется некоторый опыт. Но поскольку SQL-представлений деревьев запросов достаточно для понимания системы правил, в этой главе не будет обучения тому, как их читать.
При прочтении SQL-представлений деревьев запросов в этой главе необходимо уметь идентифицировать части, на которые разбивается оператор в структуре дерева запроса. Дерево запроса состоит из следующих частей:
тип команды
Это простое значение, говорящее, какая команда (SELECT, INSERT, UPDATE,
DELETE) создала это дерево запроса.
перечень выборки
Перечень выборки представляет собой список отношений, используемых в запросе.
В операторе SELECT это отношения, заданные после ключевого слова FROM.
Каждая запись перечня выборки идентифицирует таблицу или представление и говорит, каким именем они называются в других частях запроса. В дереве запроса на записи перечня выборки ссылаются по номеру, а не по имени, поэтому, в отличие от оператора SQL, не имеет значения, повторяются ли эти имена. Это может стать актуальным после слияния перечней выборки разных правил. В примерах в этой главе такая ситуация освещаться не будет.
результирующее отношение
Это индекс в перечне выборки, идентифицирующий отношение, в которое поступает результат запроса.
У запросов SELECT нет результирующего отношения. (Особый случай SELECT INTO
почти идентичен CREATE TABLE с последующим INSERT ... SELECT и здесь отдельно
не рассматривается.)
Для команд INSERT, UPDATE и DELETE результирующее отношение представляет
собой таблицу (или представление!), которую затрагивают изменения.
целевой список
Целевой список — это список выражений, определяющих результат запроса. В случае
SELECT это выражения, формирующие окончательный вывод запроса. Они соответствуют
выражениям между ключевыми словами SELECT и FROM. (Знак * — это
просто аббревиатура для имен всех столбцов отношения. Анализатор разворачивает
его в отдельные столбцы, поэтому система правил никогда его не видит.)
Командам DELETE не нужен обычный целевой список, поскольку они не выдают никакого
результата. Вместо этого планировщик добавляет в пустой целевой список специальную
запись CTID, чтобы исполнитель мог найти удаляемую строку. (CTID добавляется,
когда результирующее отношение является обычной таблицей. Если это представление,
вместо нее системой правил добавляется переменная, содержащая всю строку, как
описано в подразделе Изменение представления.)
Для команд INSERT в целевом списке описываются новые строки, которые должны
попасть в результирующее отношение. Он состоит из выражений в предложении VALUES
или выражений из предложения SELECT в INSERT ... SELECT. На первом этапе
процесса перезаписи добавляются записи целевого списка для всех столбцов, которым
исходная команда ничего не назначила, но имеющих значения по умолчанию. Все
оставшиеся столбцы (без заданного значения и значения по умолчанию) будут
заполнены в планировщике выражением константы NULL.
Для команд UPDATE в целевом списке описываются новые строки, которые должны
заменить старые. В системе правил он содержит только выражения из части команды
SET столбец = выражение. Планировщик обработает пропущенные столбцы, вставив
выражения, копирующие значения из старой строки в новую. Как и для команды
DELETE, добавляются CTID или переменная с целой строкой, чтобы исполнитель мог
идентифицировать изменяемую старую строку.
Каждая запись в целевом списке содержит выражение, которое может быть значением константы, переменной, указывающей на столбец одного из отношений в перечне выборки, параметром или деревом выражений, образованном из вызовов функций, констант, переменных, операторов и т. д.
условие применения
Условие применения запроса — это выражение, очень похожее на те, что содержатся
в записях целевого списка. Результат этого выражения является логическим значением,
говорящим, должна ли выполняться операция (INSERT, UPDATE, DELETE или
SELECT) для итоговой строки результата или нет. Оно соответствует предложению
WHERE оператора SQL.
дерево соединения
Дерево соединения запроса показывает структуру предложения FROM. Для простого
запроса вроде SELECT ... FROM a, b, c дерево соединения — это просто список
элементов FROM, поскольку их можно соединять в любом порядке. Но когда
используются выражения JOIN, в частности внешние соединения, следует соединять
отношения в порядке, заданном в этих выражениях. В этом случае дерево соединения
показывает структуру выражений JOIN. Ограничения, связанные с конкретными
предложениями JOIN (из выражений ON или USING), хранятся в виде
выражений условия, привязанных к этим узлам дерева соединения. Как оказалось,
выражение WHERE верхнего уровня тоже удобно хранить в виде условия применения,
привязанного к элементу верхнего уровня дерева соединения. Поэтому на самом деле
дерево соединения представляет оба предложения SELECT: FROM и WHERE.
другие
Другие части дерева запросов, например, предложение ORDER BY, не представляют интереса в данном случае. Система правил заменяет в них некоторые записи при применении правил, но это не имеет непосредственного отношения к основам системы правил.
Представления и система правил
Представления в QHB реализованы с помощью системы правил. По сути представление — это пустая таблица (не имеющая фактического хранилища) с правилом ON SELECT DO INSTEAD. Традиционно это правило называется _RETURN. Поэтому такое представление, как
CREATE VIEW myview AS SELECT * FROM mytab;
это почти то же самое, что и
CREATE TABLE myview (такой же список столбцов, как и у mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
хотя на самом деле так писать нельзя, поскольку таблицам не разрешено иметь правила ON SELECT.
Кроме того, представление может иметь другие виды правил DO INSTEAD,
позволяющие выполнять для него команды INSERT, UPDATE или DELETE, несмотря
на отсутствие нижележащего хранилища. Подробнее это рассматривается ниже, в
подразделе Изменение представления.
Как работают правила 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 (самого простого из имеющихся)
создаст отношение shoelace и запись в pg_rewrite, говорящую, что есть
правило перезаписи, которое должно применяться всякий раз, когда в перечне выборки
запроса упоминается отношение shoelace. Для этого правила нет условия применения
(они рассматриваются ниже, для правил, отличных от SELECT, поскольку в настоящее
время у правил 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 и деревом для
любой другой команды. Очевидно, у них разный тип команды, и для команды, отличной
от 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)
добавляется еще одна запись: идентификатор текущего кортежа (Current Tuple ID,
CTID). Это системный столбец, содержащий номер блока в файле и позицию строки в
блоке. Зная таблицу, можно использовать CTID для получения исходной изменяемой
строки t1. После добавления в целевой список CTID запрос фактически выглядит
так:
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
Теперь перейдем к другой особенности QHB. Старые строки таблицы
не переписываются, поэтому ROLLBACK выполняется быстро. В UPDATE в таблицу
добавляется новая результирующая строка (после удаления CTID), и в заголовке
старой строки, на которую указывает CTID, в записях cmax и xmax устанавливаются
счетчик текущей команды и идентификатор текущей транзакции. Таким образом, старая
строка скрывается, и после фиксации транзакции процесс очистки может окончательно
удалить нерабочую строку.
Зная все это, мы можем просто применять правила представлений абсолютно таким же образом к любой команде. Никакой разницы не будет.
Потенциал представлений в QHB
Примеры выше демонстрируют, как система правил встраивает определения представлений
в исходное дерево запроса. Во втором примере простой SELECT для одного
представления создал итоговое дерево запроса, представляющее собой соединение 4
таблиц (таблица unit была использована дважды с разными именами).
Преимущество реализации представлений через систему правил состоит в том, что планировщик получает всю информацию о том, какие таблицы должны быть просканированы, о взаимоотношениях между этими таблицами, об ограничивающих условиях применения в представлениях и об условиях применения из исходного запроса в одном дереве запроса. И при все при том исходный запрос уже является соединением представлений. Планировщик должен выбрать лучший способ выполнить запрос, и чем больше информации он получит, тем лучше может быть его выбор. И то, как система правил реализована в QHB, гарантирует, что это будет вся доступная на данный момент информация о запросе.
Изменение представления
Что произойдет, если указать представление в качестве целевого отношения для
INSERT, UPDATE или DELETE? Если провести описанные выше подстановки, это
даст дерево запроса, в котором результирующее отношение указывает на запись
подзапроса в перечне выборки, что не будет работать. Тем не менее существует
несколько способов, какими QHB может поддержать изменяемость
представления. Вот эти способы в порядке увеличения сложности для пользователя:
автоматические подстановки в нижележащей таблице представления, выполнение
пользовательского триггера или перезапись запроса с помощью пользовательского
правила. Все эти варианты рассматриваются ниже.
Если подзапрос выбирает из одного базового отношения и он достаточно прост, процесс
перезаписи может автоматически заменить его нижележащим базовым отношением, чтобы
команды INSERT, UPDATE или DELETE применялись к этому базовому отношению
надлежащим образом. Представления, «достаточно простые» для этого, называются
автоматически изменяемыми. Подробную информацию о видах представлений, которые
могут изменяться автоматически, см. на справочной странице команды CREATE VIEW.
Как вариант, эту операцию может проделать предоставленный пользователем триггер
INSTEAD OF для представления (см. CREATE TRIGGER). В этом случае
перезапись работает немного по другому. Для INSERT процесс перезаписи вообще
ничего не делает в представлении, оставляя его как результирующее отношение для
запроса. Для UPDATE и DELETE ему по-прежнему необходимо развернуть запрос
представления, чтобы выдать «старые» строки, которые команда попытается изменить
или удалить. Поэтому представление разворачивается как обычно, но в запрос
добавляется еще одна неразвернутая запись перечня выборки, чтобы обозначать
представление в качестве результирующего отношения.
При этом возникает проблема идентификации изменяемых строк в представлении.
Вспомните, что когда результирующее отношение является таблицей, в целевой список
добавляется специальная запись CTID для идентификации физического расположения
изменяемых строк. Это не работает, если результирующее отношение является
представлением, поскольку у представлений нет CTID, так как у их строк нет реального
физического расположения. Вместо этого для операции UPDATE или DELETE в
целевой список добавляется специальная запись wholerow, в которой при
развертывании содержатся все столбцы представления. Исполнитель использует это
значение, чтобы передать «старую» строку триггеру INSTEAD OF. Что именно
нужно изменять, решает сам триггер, основываясь на значениях старых и новых
строк.
Еще один способ — определение пользователем правил INSTEAD, указывающих
действия подстановки для команд INSERT, UPDATE и DELETE для представления.
Эти правила перезаписывают команду, обычно в другую команду, изменяющую одну или
несколько таблиц, а не представления. Эта тема рассматривается в разделе
Правила для INSERT, UPDATE и DELETE.
Обратите внимание, что правила вычисляются первыми, перезаписывая исходный запрос
до его планирования и выполнения. Поэтому если у представления есть триггеры
INSTEAD OF, а также правила для INSERT, UPDATE или DELETE, то сначала
будут вычисляться правила, и в зависимости от результата триггеры могут вообще
не задействоваться.
Автоматическая перезапись запроса INSERT, UPDATE или DELETE для простого
представления всегда проводится в последнюю очередь. Поэтому если у представления
есть правила или триггеры, они переопределят стандартное поведение автоматически
изменяемых представлений.
Если для представления нет правил INSTEAD или триггеров INSTEAD OF, и процесс перезаписи не может автоматически переписать запрос в виде изменения нижележащего базового отношения, возникнет ошибка, поскольку исполнитель не может изменить представление как таковое.
Материализованные представления
Материализованные представления в QHB используют систему правил так же, как представления, но сохраняют результаты в форме таблицы. Основные различия между:
CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;
и:
CREATE TABLE mymatview AS SELECT * FROM mytab;
состоят в том, что материализованное представление впоследствии нельзя будет изменить напрямую и что запрос, используемый для создания материализованного представления, сохраняется точно так же, как и запрос представления, поэтому новые данные для материализованного представления можно сгенерировать так:
REFRESH MATERIALIZED VIEW mymatview;
Информация о материализованном представлении в системных каталогах QHB ровно такая же, как для таблицы или представления. Поэтому для анализатора материализованное представление является отношением, совсем как таблица или представление. Когда к материализованному представлению обращаются в запросе, данные возвращаются непосредственно из него, как из таблицы; правило используется только для наполнения материализованного представления.
Хотя обращение к данным, хранящимся в материализованном представлении, зачастую выполняется гораздо быстрее, чем обращение напрямую к нижележащим таблицам или через представление, данные в нем не всегда актуальные; тем не менее иногда в актуальных данных нет нужды. Рассмотрим таблицу, где фиксируются продажи:
CREATE TABLE invoice (
invoice_no integer PRIMARY KEY,
seller_no integer, -- идентификатор продавца
invoice_date date, -- дата продажи
invoice_amt numeric(13,2) -- сумма продажи
);
Если люди хотят иметь возможность быстро построить график исторических данных по продажам, возможно, им понадобится сводная информация и не будет заботить полнота данных на текущий момент:
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
seller_no,
invoice_date,
sum(invoice_amt)::numeric(13,2) as sales_amt
FROM invoice
WHERE invoice_date < CURRENT_DATE
GROUP BY
seller_no,
invoice_date;
CREATE UNIQUE INDEX sales_summary_seller
ON sales_summary (seller_no, invoice_date);
Это материализованное представление может быть полезно для отображения графика в информационной панели, созданной для продавцов. Для еженощного обновления статистики можно с помощью этого оператора запланировать задание по расписанию:
REFRESH MATERIALIZED VIEW sales_summary;
Еще одно применение материализованного представления — предоставление более быстрого доступа к данным, передаваемым из удаленной системы через обертку сторонних данных. Ниже представлен простой пример с использованием обертки file_fdw, с привязками по времени, но поскольку при этом использовался кеш локальной системы, выигрыш в производительности по сравнению с обращением к удаленной системе обычно будет больше, чем показано здесь. Обратите внимание, что мы также использовали возможность установить индекс для материализованного представления, тогда как file_fdw не поддерживает индексы; это преимущество может оказаться неприменимым при других видах доступа к сторонним данным.
Конфигурация:
CREATE EXTENSION file_fdw;
CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE words (word text NOT NULL)
SERVER local_file
OPTIONS (filename '/usr/share/dict/words');
CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
CREATE UNIQUE INDEX wrd_word ON wrd (word);
CREATE EXTENSION pg_trgm;
CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
VACUUM ANALYZE wrd;
Теперь давайте проверим правильность написания слова. Если использовать file_fdw напрямую:
SELECT count(*) FROM words WHERE word = 'caterpiler';
count
-------
0
(1 row)
Выполнив команду EXPLAIN ANALYZE, мы увидим:
Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
-> Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
Filter: (word = 'caterpiler'::text)
Rows Removed by Filter: 479829
Foreign File: /usr/share/dict/words
Foreign File Size: 4953699
Planning time: 0.118 ms
Execution time: 188.273 ms
Если вместо этого применить материализованное представление, запрос выполняется гораздо быстрее:
Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
-> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: (word = 'caterpiler'::text)
Heap Fetches: 0
Planning time: 0.164 ms
Execution time: 0.117 ms
В любом случае слово записано неправильно, поэтому давайте поищем то, что, видимо, нам требовалось на самом деле. Опять же, сначала воспользуемся file_fdw и pg_trgm:
SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
word
---------------
cater
caterpillar
Caterpillar
caterpillars
caterpillar's
Caterpillar's
caterer
caterer's
caters
cater's
catered
(10 rows)
Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
-> Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
Sort Key: ((word <-> 'caterpiler'::text))
Sort Method: top-N heapsort Memory: 25kB
-> Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
Foreign File: /usr/share/dict/words
Foreign File Size: 4953699
Planning time: 0.128 ms
Execution time: 1431.679 ms
Теперь применим материализованное представление:
Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
-> Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
Order By: (word <-> 'caterpiler'::text)
Planning time: 0.196 ms
Execution time: 198.640 ms
Если вы не против периодического обновления данных из удаленных систем в локальной базе данных, выигрыш в производительности может быть существенным.
Правила для INSERT, UPDATE и DELETE
Правила, которые определены для INSERT, UPDATE и DELETE, значительно
отличаются от правил представлений, описанных в предыдущих разделах. Во-первых,
их команда CREATE RULE позволяет больше:
-
Им разрешено не иметь действий.
-
Им можно иметь несколько действий.
-
Они могут выполняться в режиме INSTEAD или ALSO (также, по умолчанию).
-
Псевдоотношения NEW и OLD становятся полезными.
-
Они могут иметь условия применения.
Во-вторых, они не модифицируют имеющееся дерево запроса. Вместо этого они создают ноль или более новых деревьев запросов и могут отбросить исходное.
ВНИМАНИЕ!
Во многих случаях задачи, которые могут выполняться с правилами дляINSERT/UPDATE/DELETE, лучше выполняются с триггерами. Триггеры чуть более сложны нотационно, но их семантику гораздо проще понять. Правила имеют обыкновение приводить к неожиданным результатам, когда исходный запрос содержит изменчивые функции: в процессе осуществления правил такие функции могут выполниться большее число раз, чем ожидалось.Кроме того, существует несколько случаев, вообще не поддерживаемых этими типами правил, а именно, предложения WITH в исходном запросе и вложенные
SELECTс множественным присваиванием в списке SET запросовUPDATE. Это происходит потому, что копирование этих конструкций в запрос правила привело бы к многократным вычислениям вложенного запроса, вопреки выраженному намерению автора запроса.
Как работают правила для изменения
Запомните синтаксис:
CREATE [ OR REPLACE ] RULE имя AS ON событие
TO таблица [ WHERE условие ]
DO [ ALSO | INSTEAD ] { NOTHING | команда | ( команда ; команда ... ) }
В дальнейшем правила для изменения означают правила, определяемые для INSERT,
UPDATE или DELETE.
Правила для изменения начинают применяться системой правил, когда результирующее
отношение и тип команды дерева запроса совпадают с объектом и событием, заданными
в команде CREATE RULE. Для правил для изменения система правил создает список
деревьев запросов. Изначально этот список пуст. У правила может быть ноль (ключевое
NOTHING слово), одно или несколько действий. Простоты ради мы рассмотрим
правило с одним действием. Это правило может иметь или не иметь условие применения
и может выполняться в режиме INSTEAD или ALSO (по умолчанию).
Что такое условие применения правила? Это ограничение, которое говорит, когда действия правила должны применяться, а когда — нет. Это условие применение может обращаться только к псевдоотношениям NEW и/или OLD, которые по сути представляют отношение, заданное в качестве объекта (но со специальным значением).
Итак, у нас имеется три варианта, формирующие следующие деревья запросов для правила с одним действием.
-
Без условия применения, в режиме ALSO или INSTEAD
дерево запроса из действия правила с добавленным условием применения исходного дерева запроса -
Заданы условие применения и ALSO
дерево запроса из действия правила с условием применения и добавленным условием применения исходного дерева запроса -
Заданы условие применения и INSTEAD
дерево запроса из действия правила с условием применения и добавленным условием применения исходного дерева запроса; также добавлено исходное дерево запроса с условием, обратным условию применения правила
Наконец, если это правило ALSO, в список добавляется неизмененное исходное дерево запроса. Поскольку исходное дерево запроса также добавляют только правила INSTEAD с условиями применения, в итоге для правила с одним действием мы получаем на выходе одно или два дерева запросов.
Для правил ON INSERT исходный запрос (если он не подавлен INSTEAD) выполняется перед любыми действиями, добавленными правилами. Это позволяет действиям видеть добавленные строки. Но для правил ON UPDATE и ON DELETE исходный запрос выполняется после действий, добавленных правилами. Это гарантирует, что действия могут видеть изменяемые или удаляемые строки; в противном случае действия могли бы ничего не сделать, поскольку не нашли бы строк, соответствующих их условиям применения.
Деревья запросов, генерируемые из действий правил, снова попадают в систему перезаписи, где, возможно, будут применены другие правила, приводя к добавлению или сокращению деревьев запросов. Поэтому действия правила должны иметь либо другой тип команды, либо другое результирующее отношение, чем само правило, иначе этот рекурсивный процесс приведет к бесконечному циклу. (Рекурсивное разворачивание правила будет выявлено, и будет выдана ошибка.)
Деревья запросов, встречающиеся в действиях системного каталога pg_rewrite,
представляют собой всего лишь шаблоны. Поскольку они могут обращаться к записям
перечня выборки для NEW и OLD, прежде чем их можно будет использовать,
следует выполнить несколько подстановок. Для любой ссылки на NEW соответствующая
запись ищется в целевом списке исходного запроса. Если она обнаруживается, ссылка
заменяется на выражение этой записи. В противном случае NEW означает то же
самое, что и OLD (для UPDATE) или заменяется значением NULL (для INSERT).
Все ссылки на OLD заменяются на ссылки на запись перечня выборки, представляющую
собой результирующее отношение.
После того как система заканчивает применять правила для изменения, она применяет правила представлений к сформированному дереву (или деревьям) запроса. Предложения не могут добавлять новые действия для изменения, поэтому нет нужды применять правила для изменения к выводу перезаписи представления.
Пошаговое руководство по применению первого правила
Допустим, мы хотим отслеживать изменения в столбце sl_avail в отношении
shoelace_data. Так что мы настраиваем таблицу журнала и правило, которое делает
запись в журнале по условию, когда для shoelace_data выполняется UPDATE.
CREATE TABLE shoelace_log (
sl_name text, -- шнурки, количество которых изменилось
sl_avail integer, -- новое доступное значение
log_who text, -- кто это сделал
log_when timestamp -- когда
);
CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
WHERE NEW.sl_avail <> OLD.sl_avail
DO INSERT INTO shoelace_log VALUES (
NEW.sl_name,
NEW.sl_avail,
current_user,
current_timestamp
);
Теперь некто выполняет:
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
и мы видим в таблице журнала:
SELECT * FROM shoelace_log;
sl_name | sl_avail | log_who | log_when
---------+----------+---------+----------------------------------
sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST
(1 row)
Именно на это мы и рассчитывали. При этом на заднем плане происходит следующее. Анализатор создал дерево запроса:
UPDATE shoelace_data SET sl_avail = 6
FROM shoelace_data shoelace_data
WHERE shoelace_data.sl_name = 'sl7';
Имеется правило log_shoelace с характеристикой ON UPDATE (при изменении) и выражением условия применения правила:
NEW.sl_avail <> OLD.sl_avail
а также с действием:
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old;
(Это выглядит несколько странно, поскольку обычно нельзя написать INSERT ... VALUES ... FROM. Здесь предложение FROM указано только для того, чтобы обозначить,
что в дереве запросов есть записи перечня выборки для new и old. Они
нужны для того, чтобы к ним могли обращаться переменные в дереве запроса команды
INSERT.)
Это правило ALSO с условием применения, поэтому система правил должна вернуть два дерева запросов: измененное действие правила и исходное дерево запроса. На этапе 1 перечень выборки оригинального запроса вставляется в дерево запроса действия правила. Это приводит к следующему:
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data;
На этапе 2 в это дерево добавляется условие применения правила, поэтому результирующий набор ограничивается строками, в которых меняется sl_avail:
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE new.sl_avail <> old.sl_avail;
(Это выглядит еще более странно, поскольку у INSERT ... VALUES тоже нет
предложения WHERE, но у планировщика и исполнителя затруднений с этим не
возникнет. Им в любом случае нужно поддерживать такую же функциональность для
INSERT ... SELECT.)
На этапе 3 добавляется условие применения исходного дерева запроса, еще сильнее ограничивая результирующий набор и оставляя в нем только те строки, которые затронул бы исходный запрос:
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE new.sl_avail <> old.sl_avail
AND shoelace_data.sl_name = 'sl7';
На этапе 4 ссылки на NEW заменяются записями целевого списка из исходного дерева запроса или ссылками на соответствующие переменные из результирующего отношения:
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, 6,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE 6 <> old.sl_avail
AND shoelace_data.sl_name = 'sl7';
На этапе 5 ссылки на OLD заменяются ссылками результирующего отношения:
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, 6,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE 6 <> shoelace_data.sl_avail
AND shoelace_data.sl_name = 'sl7';
Вот и все. Поскольку это правило ALSO, мы также выводим исходное дерево запроса. Говоря вкратце, вывод системы правил представляет собой список с двумя деревьями запросов, соответствующими этим операторам:
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, 6,
current_user, current_timestamp )
FROM shoelace_data
WHERE 6 <> shoelace_data.sl_avail
AND shoelace_data.sl_name = 'sl7';
UPDATE shoelace_data SET sl_avail = 6
WHERE sl_name = 'sl7';
Они выполняются точно в таком порядке, и именно это должно делать правило.
Подстановки и добавленные условия применения гарантируют, что если исходный запрос был бы, к примеру, таким:
UPDATE shoelace_data SET sl_color = 'green'
WHERE sl_name = 'sl7';
запись в журнале не была бы создана. В этом случае исходное дерево запроса не содержит записи целевого списка для sl_avail, поэтому NEW.sl_avail будет заменено на shoelace_data.sl_avail. Таким образом, дополнительной командой, сгенерированной этим правилом, является:
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, shoelace_data.sl_avail,
current_user, current_timestamp )
FROM shoelace_data
WHERE shoelace_data.sl_avail <> shoelace_data.sl_avail
AND shoelace_data.sl_name = 'sl7';
и это условие применение никогда не будет выполняться.
Это также будет работать, если исходный запрос модифицирует несколько строк. Так что, если кто-то выполнил команду:
UPDATE shoelace_data SET sl_avail = 0
WHERE sl_color = 'black';
фактически изменяются четыре строки (sl1, sl2, sl3 и sl4). Но для sl3
иже установлено sl_avail = 0. В этом случае условие применения исходного дерева
запроса отличается, и это приводит к генерированию правилом дополнительного
дерева запроса:
INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
current_user, current_timestamp
FROM shoelace_data
WHERE 0 <> shoelace_data.sl_avail
AND shoelace_data.sl_color = 'black';
Это дерево запроса определенно добавит в журнал три новые записи. И это абсолютно правильно.
Здесь мы можем видеть, почему так важно, чтобы исходное дерево запроса выполнялось
последним. Если бы команда UPDATE выполнилась первой, все строки уже были бы
установлены в ноль, так что процесс журналирования INSERT не нашел бы ни одной
строки, где 0 <> shoelace_data.sl_avail.
Взаимодействие с представлениями
Простой способ защитить отношения представлений от упомянутой ранее вероятности,
что кто-то может попытаться выполнить для них INSERT, UPDATE или DELETE —
это позволить отбрасывать такие деревья запросов. Поэтому мы можем создать
следующие правила:
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
DO INSTEAD NOTHING;
Теперь, если кто-то попытается выполнить любую из этих операций для отношения представления shoe, система правил применит эти правила. Поскольку у этих правил нет действий и они выполняются в режиме INSTEAD, результирующий список деревьев запросов будет пуст и весь запрос нивелируется, поскольку после его обработки системой запросов не останется ничего, что можно оптимизировать или выполнить.
Более сложный способ применения системы правил состоит в создании правил, переписывающих дерево запроса в выполняющее правильную операцию с реальными таблицами. Чтобы сделать это с представлением shoelace, мы создадим следующие правила:
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
DO INSTEAD
INSERT INTO shoelace_data VALUES (
NEW.sl_name,
NEW.sl_avail,
NEW.sl_color,
NEW.sl_len,
NEW.sl_unit
);
CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
DO INSTEAD
UPDATE shoelace_data
SET sl_name = NEW.sl_name,
sl_avail = NEW.sl_avail,
sl_color = NEW.sl_color,
sl_len = NEW.sl_len,
sl_unit = NEW.sl_unit
WHERE sl_name = OLD.sl_name;
CREATE RULE shoelace_del AS ON DELETE TO shoelace
DO INSTEAD
DELETE FROM shoelace_data
WHERE sl_name = OLD.sl_name;
Если вы желаете поддерживать для представления запросы RETURNING, вам нужно создать правила, включающие предложения RETURNING, которые вычислят строки представления. Обычно это довольно просто для представлений по одной таблице, но несколько трудоемко для представлений с соединением, таких как shoelace. Рассмотрим пример с добавлением:
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
DO INSTEAD
INSERT INTO shoelace_data VALUES (
NEW.sl_name,
NEW.sl_avail,
NEW.sl_color,
NEW.sl_len,
NEW.sl_unit
)
RETURNING
shoelace_data.*,
(SELECT shoelace_data.sl_len * u.un_fact
FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
Обратите внимание, что это одно правило поддерживает как запрос INSERT, так и
запрос INSERT RETURNING к этому представлению — предложение RETURNING просто
игнорируется для INSERT.
Теперь предположим, что время от времени в магазин поступает партия шнурков с объемной номенклатурной ведомостью. Но вы не хотите каждый раз вручную обновлять представление shoelace. Вместо этого мы заводим две маленькие таблицы: в одну вы можете добавлять элементы из ведомости, а у другой есть особая хитрость. Так выглядят создающие их команды:
CREATE TABLE shoelace_arrive (
arr_name text,
arr_quant integer
);
CREATE TABLE shoelace_ok (
ok_name text,
ok_quant integer
);
CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
DO INSTEAD
UPDATE shoelace
SET sl_avail = sl_avail + NEW.ok_quant
WHERE sl_name = NEW.ok_name;
Теперь вы можете заполнить таблицу shoelace_arrive данными из ведомости:
SELECT * FROM shoelace_arrive;
arr_name | arr_quant
----------+-----------
sl3 | 10
sl6 | 20
sl8 | 20
(3 rows)
Быстро просмотрим текущие данные:
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 | 6 | 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)
Теперь перенесем поступившие шнурки в:
INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
и проверим результаты:
SELECT * FROM shoelace ORDER BY sl_name;
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 | 6 | brown | 60 | cm | 60
sl4 | 8 | black | 40 | inch | 101.6
sl3 | 10 | black | 35 | inch | 88.9
sl8 | 21 | brown | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 20 | brown | 0.9 | m | 90
(8 rows)
SELECT * FROM shoelace_log;
sl_name | sl_avail | log_who| log_when
---------+----------+--------+----------------------------------
sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST
sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST
sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST
sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)
От одной INSERT ... SELECT до этих результатов была проделана большая работа.
И в конце этой главы будет описано преобразование дерева запроса. Начнем с вывода
анализатора:
INSERT INTO shoelace_ok
SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
Теперь применяется первое правило shoelace_ok_ins, превращающее вывод в:
UPDATE shoelace
SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace
WHERE shoelace.sl_name = shoelace_arrive.arr_name;
и отбрасывающее исходный INSERT для shoelace_ok. Этот переписанный запрос
снова передается в систему правил, и второе применяемое правило shoelace_upd
выдает:
UPDATE shoelace_data
SET sl_name = shoelace.sl_name,
sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
sl_color = shoelace.sl_color,
sl_len = shoelace.sl_len,
sl_unit = shoelace.sl_unit
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data
WHERE shoelace.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = shoelace.sl_name;
Это тоже правило INSTEAD, поэтому предыдущее дерево запроса отбрасывается. Обратите внимание, что этот запрос по-прежнему использует представление shoelace. Но система правил еще не закончила с этим этапом, поэтому она продолжает и применяет к нему правило _RETURN, и мы получаем:
UPDATE shoelace_data
SET sl_name = s.sl_name,
sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
sl_color = s.sl_color,
sl_len = s.sl_len,
sl_unit = s.sl_unit
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data,
shoelace old, shoelace new,
shoelace_data s, unit u
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name;
Наконец, применяется правило log_shoelace, выдавая дополнительное дерево запроса:
INSERT INTO shoelace_log
SELECT s.sl_name,
s.sl_avail + shoelace_arrive.arr_quant,
current_user,
current_timestamp
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data,
shoelace old, shoelace new,
shoelace_data s, unit u,
shoelace_data old, shoelace_data new
shoelace_log shoelace_log
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name
AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail;
После этого у системы правил кончаются правила, и она возвращает сгенерированные деревья запросов.
Так что в результате мы получаем два итоговых дерева запросов, равнозначных следующим операторам SQL:
INSERT INTO shoelace_log
SELECT s.sl_name,
s.sl_avail + shoelace_arrive.arr_quant,
current_user,
current_timestamp
FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
shoelace_data s
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name
AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail;
UPDATE shoelace_data
SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive,
shoelace_data shoelace_data,
shoelace_data s
WHERE s.sl_name = shoelace_arrive.sl_name
AND shoelace_data.sl_name = s.sl_name;
В результате данные, поступающие из одного отношения, добавляются в другое превращаются в изменения в третьем, превращаются в изменение четвертого, а журналирование этого итогового изменения в пятое отношение сводится к двум запросам.
Здесь имеется маленькая, но немного некрасивая деталь. Если взглянуть на эти два
запроса, оказывается, что отношение shoelace_data фигурирует в перечне выборки
дважды, хотя его определенно можно сократить до одного. Планировщик это не
воспринимает, поэтому план выполнения для вывода системы правил оператора
INSERT будет выглядеть так:
Nested Loop
-> Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on s
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_arrive
-> Seq Scan on shoelace_data
тогда как при избавлении от лишней записи перечня выборки мы получим
Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on s
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_arrive
для которого в таблице журнала будут выведены точно такие же записи. Таким образом,
из-за системы правил проводится одно дополнительное сканирование таблицы
shoelace_data, в котором совершенно нет необходимости. И такое же лишнее
сканирование проводится еще раз в UPDATE. Однако сделать все это в принципе
возможным и без того было действительно трудно.
Теперь мы проведем завершающую демонстрацию системы правил QHB и ее потенциала. Допустим, вы добавляете в свою базу данные шнурки с экстраординарными цветами:
INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
Мы хотели бы создать представление, чтобы проверить, какие шнурки (записи в shoelace) не подходят по цвету ни к каким туфлям. Представление для этого выглядит так:
CREATE VIEW shoelace_mismatch AS
SELECT * FROM shoelace WHERE NOT EXISTS
(SELECT shoename FROM shoe WHERE slcolor = sl_color);
Это его вывод:
SELECT * FROM shoelace_mismatch;
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
sl9 | 0 | pink | 35 | inch | 88.9
sl10 | 1000 | magenta | 40 | inch | 101.6
Теперь мы хотим настроить его так, чтобы неподходящие шнурки, которых нет на складе, удалялись из базы данных. Чтобы немного усложнить задачу для QHB, мы не будем удалять их напрямую. Вместо этого мы создаем еще одно представление:
CREATE VIEW shoelace_can_delete AS
SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
и удалим их так:
DELETE FROM shoelace WHERE EXISTS
(SELECT * FROM shoelace_can_delete
WHERE sl_name = shoelace.sl_name);
Результатом будет:
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 | 6 | brown | 60 | cm | 60
sl4 | 8 | black | 40 | inch | 101.6
sl3 | 10 | black | 35 | inch | 88.9
sl8 | 21 | brown | 40 | inch | 101.6
sl10 | 1000 | magenta | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 20 | brown | 0.9 | m | 90
(9 rows)
Запрос DELETE для представления, с условием применения в подзапросе, использующий
в общей сложности 4 вложенных/соединенных представления, где одно из них само
имеет подзапрос с условием применения, содержащий представление, и где применяются
вычисляемые столбцы представлений, переписывается в единственное дерево запроса,
которое удаляет требуемые данные из реальной таблицы.
В реальности ситуаций, когда такая конструкция необходима, крайне мало. Но тем приятнее осознавать, что она работает.
Правила и права
Вследствие переписывания запросов системой правил QHB обращение происходит не к тем таблицам/представлениям, которые использовались в исходном запросе. Когда применяются правила для изменения, сюда может входить также доступ на запись в таблицы.
У правил перезаписи нет отдельного владельца. Владелец отношения (таблицы или
представления) автоматически становится владельцем определенных для этого
отношения правил перезаписи. Система правил QHB меняет поведение
стандартной системы управления доступом. За исключением правил SELECT, связанных
с представлениями с характеристикой «безопасность вызывающего» (см. CREATE VIEW),
все отношения, используемые из-за применения правил, проверяются на предмет прав
владельца правила, а не пользователя, который это правило вызвал. Это означает,
что, за исключением представлений с безопасностью вызывающего, пользователям
нужны только те права, требуемые для таблиц/представлений, которые явно указаны
в их запросах.
Например: У пользователя есть список телефонных номеров, некоторые из которых личные, а другие интересуют помощника в офисе. Пользователь может сконструировать следующее:
CREATE TABLE phone_data (person text, phone text, private boolean);
CREATE VIEW phone_number AS
SELECT person, CASE WHEN NOT private THEN phone END AS phone
FROM phone_data;
GRANT SELECT ON phone_number TO assistant;
Никто, кроме этого пользователя (и суперпользователей базы данных), не может
обратиться к таблице phone_data. Но благодаря команде GRANT помощник может
выполнить SELECT для представления phone_number. Система правил перепишет
SELECT из phone_number в SELECT из phone_data. Поскольку пользователь
является владельцем phone_number, а значит и владельцем правила, теперь
доступ на чтение phone_data проверяется относительно прав этого пользователя,
и выполнение запроса разрешается. Также выполняется проверка прав доступа к
phone_number, но она проводится для вызывающего пользователя, поэтому
использовать его может только сам пользователь и его помощник.
Права проверяются правило за правилом. Поэтому на данный момент только помощник может видеть открытые телефонные номера. Но помощник может создать другое представление и сделать его общедоступным. После этого любой сможет видеть данные phone_number через представление помощника. Что помощник не может сделать, так это создать представление, обращающееся к phone_data напрямую. (Вообще-то помощник может это сделать, но оно не будет работать, поскольку всякий раз в доступе будет отказано во время проверок прав.) И как только пользователь заметит, что помощник открыл его представление phone_number, он может отозвать у помощника права. Сразу после этого любое обращение к представлению помощника будет завершаться неудачей.
Может показаться, что такая проверка «правило-за-правилом» является брешью в
защите, но на самом деле это не так. Но даже если бы она не работала таким образом,
помощник мог бы создать таблицу с такими же столбцами, как в phone_number, и
раз в день копировать туда данные. Тогда это были бы собственные данные помощника,
и он мог бы предоставлять доступ к ним кому угодно. Команда GRANT означает
«я доверяю тебе». Если кто-то, кому вы доверяете, делает нечто подобное, самое
время задуматься об этом и лишить его прав с помощью REVOKE.
Обратите внимание, что хотя представления можно использовать для сокрытия содержимого определенных столбцов с помощью показанной выше методики, их нельзя использовать для надежного сокрытия данных в невидимых строках, если только не был установлен флаг security_barrier. Например, следующее представление небезопасно:
CREATE VIEW phone_number AS
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
Это представление может показаться защищенным, поскольку система правил перепишет
любой SELECT из phone_number в SELECT из phone_data и добавит условие,
что нужны только те записи, где телефон начинается не с 412. Но если пользователь
может создавать собственные функции, нетрудно убедить планировщик выполнять
пользовательскую функцию перед выражением NOT LIKE. Например:
CREATE FUNCTION tricky(text, text) RETURNS bool AS $$
BEGIN
RAISE NOTICE '% => %', $1, $2;
RETURN true;
END;
$$ LANGUAGE plpgsql COST 0.0000000000000000000001;
SELECT * FROM phone_number WHERE tricky(person, phone);
Каждый человек и телефонный номер в таблице phone_data будут выведены в виде сообщения NOTICE, поскольку планировщик предпочтет выполнить недорогую функцию tricky перед более дорогим NOT LIKE. Даже если пользователю не разрешено определять новые функции, для подобных атак можно воспользоваться и встроенными функциями. (К примеру, большинство функций приведения вставляют свои входные значения в выдаваемые ими сообщения об ошибке.)
Похожие соображения применяются и к правилам для изменения. В примерах предыдущего
раздела владелец таблиц в базе данных мог предоставить кому-нибудь другому права
SELECT, INSERT, UPDATE и DELETE для представления shoelace, но для
shoelace_log — только SELECT. Действие правила, добавляющее записи в журнал,
по-прежнему будет успешно выполняться, и этот другой пользователь сможет видеть
записи журнала. Но он не сможет ни создавать поддельные записи, ни изменять или
удалять существующие. В этом случае нет никакой возможности нарушить правила,
убедив планировщик изменить порядок операций, поскольку единственное правило,
которое обращается к shoelace_log, это безусловный INSERT. В более сложных
сценариях это может не работать.
Когда нужно, чтобы представление обеспечивало защиту на уровне строк, к нему следует применить атрибут security_barrier. Это предотвращает передачу значений из строк злонамеренно выбранными функциями и операторами до того, как представление завершит свою работу. Например, показанное выше представление было бы безопасным, если бы было создано следующим образом:
CREATE VIEW phone_number WITH (security_barrier) AS
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
Представления, созданные с security_barrier, могут работать гораздо хуже, чем представления, созданные без этого параметра. В целом, способа избежать этого не существует: самый быстрый из возможных план должен быть отвергнут, если он может скомпрометировать защиту. По этой причине данный параметр по умолчанию выключен.
Планировщик запросов обладает большей гибкостью, работая с функциями, не имеющими побочных эффектов. Такие функции называются герметичными (LEAKPROOF) и включают множество простых широко используемых операторов, например, операторы равенства. Планировщик запросов может безопасно позволить таким функциям вычисляться в любой момент вычисления запроса, поскольку при их вызове для строк, невидимых для пользователя, никакая информация об этих строках не просочится. Более того, функции, которые не принимают аргументы или которым не передаются аргументы из представления с барьером безопасности, необязательно помечать как LEAKPROOF, чтобы передать дальше, поскольку они не получают данные из представления. Напротив, функция, которая может выдать ошибку в зависимости от значений, полученных в качестве аргумента (например, та, которая выдает ошибку в случае переполнения или деления на ноль), не является герметичной и может выдать существенную информацию о невидимых строках, если будет применяться перед фильтрами строк барьера безопасности представления.
Важно понимать, что даже представление, созданное с параметром security_barrier,
рассматривается как безопасное только в ограниченном смысле — что содержимое
невидимых кортежей не будет передано потенциально небезопасным функциям. У
пользователя вполне могут быть другие средства извлечения конфиденциальной
информации о невидимых данных; к примеру, он может увидеть план запроса с помощью
EXPLAIN или замерить время выполнения запросов к этому представлению.
Злоумышленник может сделать выводы об объеме невидимых данных или даже собрать
некоторые сведения о распределении данных или наиболее распространенных значениях
(поскольку все это может повлиять на время выполнения плана или даже, учитывая,
что это также отражается в статистике оптимизатора, на выбор плана). Если эти
типы атак через «скрытые каналы» вызывают опасения, возможно, разумно будет
вообще не предоставлять никакого доступа к этим данным.
Правила и статус команд
Сервер QHB возвращает строку статуса команды, например,
INSERT 149592 1, для каждой получаемой команды. Это довольно просто, когда не
задействуются правила, но что произойдет, когда запрос будет переписан правилами?
Правила влияют на статус команды следующим образом:
-
Если для запроса нет безусловного правила INSTEAD, то выполнится заданный исходный запрос, и его статус команды вернется как обычно. (Но учтите, что если у него были какие-либо условные правила INSTEAD, к исходному запросу будет добавлено условие, обратное их условиям применения. Это может уменьшить количество обрабатываемых запросом строк и повлиять на выводимый статус команды.)
-
Если для запроса имеется какое-либо безусловное правило INSTEAD, то исходный запрос вообще не будет выполняться. В этом случае сервер вернет статус команды для последнего запроса, вставленного правилом INSTEAD (условным или безусловным) и имеющего тот же тип команды (
INSERT,UPDATEилиDELETE), что и исходный запрос. Если никаким правилом не был добавлен ни один запрос, отвечающий этим требованиям, то в возвращенном статусе команды отображается тип исходного запроса и нули вместо полей количества строк и OID.
Программист может сделать так, чтобы статус команды во втором случае устанавливало любое желаемое правило INSTEAD, назначив ему имя, стоящее по алфавиту после других активных правил, чтобы это правило применялось последним.
Сравнение правил и триггеров
Многие задачи, которые можно выполнить с помощью триггеров, можно также реализовать с помощью системы правил QHB. Одной из задач, которые нельзя реализовать с помощью правил, являются некоторые виды ограничений, в частности, внешние ключи. Конечно, можно установить условное правила, переписывающее команду в NOTHING, если значение столбца отсутствует в другой таблице, но тогда данные будут автоматически отбрасываться, а это не самая лучшая идея. И если требуются проверки на допустимость значений, а в случае обнаружения недопустимого значения должно выдаваться сообщение об ошибке, это следует делать с помощью триггера.
В этой главе мы сосредоточились на использовании правил для изменения представлений. Все примеры правил для изменения в этой главе можно также реализовать с помощью триггеров INSTEAD OF для представлений. Написать такие триггеры зачастую проще, чем написать правила, особенно если для выполнения изменения требуется сложная логика.
Для задач, которые можно решить обоими механизмами, выбор наилучшего зависит от способа использования базы данных. Триггер срабатывает для каждой затронутой строки. Правило модифицирует запрос или генерирует дополнительный запрос. Поэтому если в одном операторе задействовано много строк, правило, создающее одну дополнительную команду, скорее всего будет работать быстрее, чем триггер, который вызывается для каждой отдельной строки и должен каждый раз заново определять, что с ней делать. Однако метод триггеров концептуально гораздо проще метода правил, и новичку гораздо легче выполнить его правильно.
Далее мы покажем пример того, как выбор в пользу правил вместо триггеров оправдывает себя в конкретной ситуации. Допустим, у нас есть две таблицы:
CREATE TABLE computer (
hostname text, -- индексировано
manufacturer text -- индексировано
);
CREATE TABLE software (
software text, -- индексировано
hostname text -- индексировано
);
Обе таблицы содержат несколько тысяч строк, а индексы по полю hostname являются уникальными. Правило или триггер должны реализовать ограничение, которое удаляет строки из таблицы software, ссылающиеся на удаляемый компьютер. Триггер воспользовался бы этой командой:
DELETE FROM software WHERE hostname = $1;
Поскольку триггер вызывается для каждой отдельной строки, удаляемой из таблицы computer, он может подготовить и сохранить план для этой команды и передать значение hostname в параметре. Правило было бы написано как:
CREATE RULE computer_del AS ON DELETE TO computer
DO DELETE FROM software WHERE hostname = OLD.hostname;
Теперь взглянем на разные типы удаления. В этом случае:
DELETE FROM computer WHERE hostname = 'mypc.local.net';
таблица computer сканируется по индексу (быстро), и команда, выполняемая триггером, тоже применила бы индексное сканирование (тоже быстро). Дополнительной командой из правила была бы:
DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
AND software.hostname = computer.hostname;
Поскольку созданы все подходящие индексы, планировщик создаст план
Nestloop
-> Index Scan using comp_hostidx on computer
-> Index Scan using soft_hostidx on software
Таким образом, большой разницы в скорости между реализацией с триггером и реализацией с правилом не будет.
При следующем удалении нам нудно избавиться ото всех 2000 компьютеров, у которых hostname начинается с old. Это можно сделать двумя командами. Одна:
DELETE FROM computer WHERE hostname >= 'old'
AND hostname < 'ole'
Командой, добавленной правилом, будет:
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
AND software.hostname = computer.hostname;
с планом
Hash Join
-> Seq Scan on software
-> Hash
-> Index Scan using comp_hostidx on computer
Другая возможная команда:
DELETE FROM computer WHERE hostname ~ '^old';
которая приводит к созданию следующего плана выполнения для команды, добавленной правилом:
Nestloop
-> Index Scan using comp_hostidx on computer
-> Index Scan using soft_hostidx on software
Это показывает, что планировщик не понимает, что условие применение для hostname
в computer можно также использовать для сканирования по индексу в software,
когда есть несколько выражений условия, объединенных с помощью AND, как он
делает в версии команды с регулярным выражением. Триггер будет вызываться для
каждого из 2000 старых компьютеров, которые нужно удалить, и это приведет к одному
сканированию индекса в computer и 2000 сканированиям индекса в software.
Реализация правила сделает это с помощью двух команд, использующих индексы. И
будет ли правило по-прежнему быстрее при последовательном сканировании, зависит
от общего размера таблицы software. Выполнение 2000 команд из триггера через
менеджер SPI занимает некоторое время, даже если все блоки индексов скоро попадут
в кеш.
Последней мы рассмотрим эту команду:
DELETE FROM computer WHERE manufacturer = 'bim';
Опять же, она может привести к удалению множества строк из таблицы computer. Поэтому триггер снова запустит через исполнитель много команд. А команда, сгенерированная правилом, будет выглядеть так:
DELETE FROM software WHERE computer.manufacturer = 'bim'
AND software.hostname = computer.hostname;
План для этой команды снова будет вложенным циклом по двум сканированиям индекса, но с использованием другого индекса для таблицы computer:
Nestloop
-> Index Scan using comp_manufidx on computer
-> Index Scan using soft_hostidx on software
Во всех этих случаях дополнительные команды от системы правил будут более или менее независимыми от количества затрагиваемых строк в команде.
Обобщая вышесказанное, правила будут значительно медленнее триггеров, только если их действия приводят к образованию больших и плохо сформированных соединений — ситуация, когда планировщик не справляется.