Чем отличаются information_schema.triggers и pg_trigger в СУБД PostgreSQL

Question

Использую PostgreSQL 13. В данный момент разбираюсь с триггерами. Возник вопрос: как я могу посмотреть, какие я создал триггеры на данный момент? Нашёл в интернете 2 способа:

Способ 1:

-- Посмотреть, какие есть триггеры:
select *
from information_schema.triggers;

Результат (не все столбцы на скриншоте):

Способ 2:

-- Посмотреть триггеры:
select *
from pg_trigger;

Результат (не все столбцы):

Первый способ выглядит чуть более информативным. А вообще есть ещё какая-то разница между таблицами information_schema.triggers и pg_trigger ?

Answer 1

Вся information_schema - это представление информации о структуре БД как то описывает спецификация SQL. То есть переносимо на другие СУБД, стандартно, структура меняется только по требованию стандарта (то есть можно сказать что не меняется), однако ожидаемо не будет отображать особенности СУБД которых нет в SQL спецификации.

information_schema.triggers - это просто view над pg_trigger (+ чуток всякого), в psql команда \d+ information_schema.triggers покажет даже как это view объявлена:

 SELECT current_database()::information_schema.sql_identifier AS trigger_catalog,
    n.nspname::information_schema.sql_identifier AS trigger_schema,
    t.tgname::information_schema.sql_identifier AS trigger_name,
    em.text::information_schema.character_data AS event_manipulation,
    current_database()::information_schema.sql_identifier AS event_object_catalog,
    n.nspname::information_schema.sql_identifier AS event_object_schema,
    c.relname::information_schema.sql_identifier AS event_object_table,
    rank() OVER (PARTITION BY (n.nspname::information_schema.sql_identifier), (c.relname::information_schema.sql_identifier), em.num, (t.tgtype::integer & 1), (t.tgtype::integer & 66) ORDER BY t.tgname)::information_schema.cardinal_number AS action_order,
        CASE
            WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN (regexp_match(pg_get_triggerdef(t.oid), '.{35,} WHEN \((.+)\) EXECUTE FUNCTION'::text))[1]
            ELSE NULL::text
        END::information_schema.character_data AS action_condition,
    SUBSTRING(pg_get_triggerdef(t.oid) FROM POSITION(('EXECUTE FUNCTION'::text) IN (SUBSTRING(pg_get_triggerdef(t.oid) FROM 48))) + 47)::information_schema.character_data AS action_statement,
        CASE t.tgtype::integer & 1
            WHEN 1 THEN 'ROW'::text
            ELSE 'STATEMENT'::text
        END::information_schema.character_data AS action_orientation,
        CASE t.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'::text
            WHEN 64 THEN 'INSTEAD OF'::text
            ELSE 'AFTER'::text
        END::information_schema.character_data AS action_timing,
    t.tgoldtable::information_schema.sql_identifier AS action_reference_old_table,
    t.tgnewtable::information_schema.sql_identifier AS action_reference_new_table,
    NULL::name::information_schema.sql_identifier AS action_reference_old_row,
    NULL::name::information_schema.sql_identifier AS action_reference_new_row,
    NULL::timestamp with time zone::information_schema.time_stamp AS created
   FROM pg_namespace n,
    pg_class c,
    pg_trigger t,
    ( VALUES (4,'INSERT'::text), (8,'DELETE'::text), (16,'UPDATE'::text)) em(num, text)
  WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid AND (t.tgtype::integer & em.num) <> 0 AND NOT t.tgisinternal AND NOT pg_is_other_temp_schema(n.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES'::text));

pg_trigger же - это часть системного каталога (pg_catalog), который действительно хранит данные об всех триггерах, включая необходимые атрибуты для обеспечения работы всех возможностей postgresql. В отличии от information_schema, объекты pg_catalog не только не обещают постоянства, но напротив обещают, что в каждом новом major релизе (например при апгрейде с postgresql 13 на 14) любое содержимое pg_catalog может меняться любым образом если это нужно для разработчиков postgresql по какой-то причине. Ключевое тут конечно "если это нужно", так что обычно pg_catalog меняется не сильно.

То есть оба способа - это одно и то же по своей сути, один и тот же источник информации. Но вот действительная разница между способами действительно есть: по требованию стандарта, обратите внимание на WHERE в процитированном мной определении этого view: оно проверяет права доступа. Запрос с pg_trigger не считает секретной структуру таблицы и покажет вам триггеры включая те, которые information_schema скроет из-за отсутствия прав у пользователя.

БЛОГ НА HUSL

Чем отличаются information_schema.triggers и pg_trigger в СУБД PostgreSQL

Ответы (1 шт):