Обсуждение: BUG #16015: information_schema.triggers lack of truncate trigger
The following bug has been logged on the website: Bug reference: 16015 Logged by: DamionZ Zhao Email address: zhq651@126.com PostgreSQL version: 11.4 Operating system: linux Description: 1.create scripts --base table CREATE TABLE student ( id int primary key, name varchar(50) ); CREATE TABLE score ( studentId int, studentname varchar(50), math int ); --function for triggers CREATE OR REPLACE FUNCTION student_delete_trigger()RETURNS TRIGGER AS $$BEGIN DELETE FROM score where studentId = OLD.id; RETURN OLD;END;$$LANGUAGE plpgsql; --delete CREATE TRIGGER delete_trigger AFTER DELETE ON student FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger(); --update CREATE TRIGGER delete_trigger_1 BEFORE update ON student FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger(); --isnert CREATE TRIGGER insert_trigger after insert ON student FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger(); --truncate CREATE TRIGGER truncate_trigger before truncate ON student FOR EACH STATEMENT EXECUTE PROCEDURE student_delete_trigger(); 2. compare 2.1 Let's see pg_trigger below. It is OK. We can see the last record : [truncate_trigger] postgres=# select *from pg_trigger where tgrelid=(select oid from pg_class where relname='student'); tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable ---------+------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+ ------------ 88223 | delete_trigger | 88231 | 9 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | | 88223 | delete_trigger_1 | 88231 | 19 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | | 88223 | insert_trigger | 88231 | 5 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | | 88223 | truncate_trigger | 88231 | 34 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | | (4 rows) 2.2 see information_schema.triggers, there is no record of [truncate_trigger] postgres=# select *from information_schema.triggers where event_object_table='student'; trigger_catalog | trigger_schema | trigger_name | event_manipulation | event_object_catalog | event_object_schema | event_object_table | action_order | action_condition | action_stateme nt | action_orientation | action_timing | action_reference_old_table | action_reference_new_table | action_reference_old_row | action_reference_new_row | created -----------------+----------------+------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------- ----------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+--------- postgres | public | insert_trigger | INSERT | postgres | public | student | 1 | | EXECUTE PROCEDURE student_d elete_trigger() | ROW | AFTER | | | | | postgres | public | delete_trigger | DELETE | postgres | public | student | 1 | | EXECUTE PROCEDURE student_d elete_trigger() | ROW | AFTER | | | | | postgres | public | delete_trigger_1 | UPDATE | postgres | public | student | 1 | | EXECUTE PROCEDURE student_d elete_trigger() | ROW | BEFORE | | | | | (3 rows) 3.see implemation of information_schema.triggers postgres=# \dS+ information_schema.triggers View "information_schema.triggers" Column | Type | Collation | Nullable | Default | Storage | Description ----------------------------+------------------------------------+-----------+----------+---------+----------+------------- trigger_catalog | information_schema.sql_identifier | | | | extended | trigger_schema | information_schema.sql_identifier | | | | extended | trigger_name | information_schema.sql_identifier | | | | extended | event_manipulation | information_schema.character_data | | | | extended | event_object_catalog | information_schema.sql_identifier | | | | extended | event_object_schema | information_schema.sql_identifier | | | | extended | event_object_table | information_schema.sql_identifier | | | | extended | action_order | information_schema.cardinal_number | | | | plain | action_condition | information_schema.character_data | | | | extended | action_statement | information_schema.character_data | | | | extended | action_orientation | information_schema.character_data | | | | extended | action_timing | information_schema.character_data | | | | extended | action_reference_old_table | information_schema.sql_identifier | | | | extended | action_reference_new_table | information_schema.sql_identifier | | | | extended | action_reference_old_row | information_schema.sql_identifier | | | | extended | action_reference_new_row | information_schema.sql_identifier | | | | extended | created | information_schema.time_stamp | | | | plain | View definition: 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.oid, c.oid, 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 PROCEDURE'::text))[1] ELSE NULL::text END::information_schema.character_data AS action_condition, "substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 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::character varying::information_schema.sql_identifier AS action_reference_old_row, NULL::character varying::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)); Look at the line, should "truncate " be here ??? ( VALUES (4,'INSERT'::text), (8,'DELETE'::text), (16,'UPDATE'::text)) em(num, text)
Hi, This is what I found in the definition of triggers views in information_schema.sql which says we are intentionally omitting truncate triggers but then there is no clear cut explanation given for this omission. -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE (VALUES (4, 'INSERT'), (8, 'DELETE'), (16, 'UPDATE')) AS em (num, text) Even the postgres documentation on triggers view doesn't have any statement saying why truncate triggers are not shown by this view. As it is being done intentionally, this is for sure not a bug but then why it has been done so can only be explained by the Author. I think the following git commit added this comment whose author is Tom so we can just wait for his reply. commit 2ec993a7cbdd8e251817ac6bbc9a704ce8346f73 Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Sun Oct 10 13:43:33 2010 -0400 Support triggers on views. This patch adds the SQL-standard concept of an INSTEAD OF trigger, which is fired instead of performing a physical insert/update/delete. The trigger function is passed the entire old and/or new rows of the view, and must figure out what to do to the underlying tables to implement the update. So this feature can be used to implement updatable views using trigger programming style rather than rule hacking. In passing, this patch corrects the names of some columns in the information_schema.triggers view. It seems the SQL committee renamed them somewhere between SQL:99 and SQL:2003. -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com On Fri, Sep 20, 2019 at 2:49 PM PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 16015 > Logged by: DamionZ Zhao > Email address: zhq651@126.com > PostgreSQL version: 11.4 > Operating system: linux > Description: > > 1.create scripts > --base table > CREATE TABLE student ( > id int primary key, > name varchar(50) > ); > CREATE TABLE score ( > studentId int, > studentname varchar(50), > math int > ); > --function for triggers > CREATE OR REPLACE FUNCTION student_delete_trigger()RETURNS TRIGGER AS > $$BEGIN DELETE FROM score where studentId = OLD.id; RETURN > OLD;END;$$LANGUAGE plpgsql; > > --delete > CREATE TRIGGER delete_trigger AFTER DELETE ON student FOR EACH ROW EXECUTE > PROCEDURE student_delete_trigger(); > --update > CREATE TRIGGER delete_trigger_1 BEFORE update ON student FOR EACH ROW > EXECUTE PROCEDURE student_delete_trigger(); > --isnert > CREATE TRIGGER insert_trigger after insert ON student FOR EACH ROW EXECUTE > PROCEDURE student_delete_trigger(); > --truncate > CREATE TRIGGER truncate_trigger before truncate ON student FOR EACH > STATEMENT EXECUTE PROCEDURE student_delete_trigger(); > > 2. compare > 2.1 Let's see pg_trigger below. It is OK. We can see the last record : > [truncate_trigger] > > postgres=# select *from pg_trigger where tgrelid=(select oid from pg_class > where relname='student'); > tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | > tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred > | tgnargs | tgattr | tgargs | tgqual | tgoldtable | > tgnewtable > ---------+------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+ > ------------ > 88223 | delete_trigger | 88231 | 9 | O | f | > 0 | 0 | 0 | f | f | > 0 | | \x | | | > > 88223 | delete_trigger_1 | 88231 | 19 | O | f | > 0 | 0 | 0 | f | f | > 0 | | \x | | | > > 88223 | insert_trigger | 88231 | 5 | O | f | > 0 | 0 | 0 | f | f | > 0 | | \x | | | > > 88223 | truncate_trigger | 88231 | 34 | O | f | > 0 | 0 | 0 | f | f | > 0 | | \x | | | > > (4 rows) > > 2.2 see information_schema.triggers, there is no record of > [truncate_trigger] > postgres=# select *from information_schema.triggers where > event_object_table='student'; > trigger_catalog | trigger_schema | trigger_name | event_manipulation | > event_object_catalog | event_object_schema | event_object_table | > action_order | action_condition | action_stateme > nt | action_orientation | action_timing | > action_reference_old_table | action_reference_new_table | > action_reference_old_row | action_reference_new_row | created > -----------------+----------------+------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------- > ----------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+--------- > postgres | public | insert_trigger | INSERT | > postgres | public | student | > 1 | | EXECUTE PROCEDURE student_d > elete_trigger() | ROW | AFTER | > | | | > | > postgres | public | delete_trigger | DELETE | > postgres | public | student | > 1 | | EXECUTE PROCEDURE student_d > elete_trigger() | ROW | AFTER | > | | | > | > postgres | public | delete_trigger_1 | UPDATE | > postgres | public | student | > 1 | | EXECUTE PROCEDURE student_d > elete_trigger() | ROW | BEFORE | > | | | > | > (3 rows) > > 3.see implemation of information_schema.triggers > > postgres=# \dS+ information_schema.triggers > View > "information_schema.triggers" > Column | Type | Collation > | Nullable | Default | Storage | Description > ----------------------------+------------------------------------+-----------+----------+---------+----------+------------- > trigger_catalog | information_schema.sql_identifier | > | | | extended | > trigger_schema | information_schema.sql_identifier | > | | | extended | > trigger_name | information_schema.sql_identifier | > | | | extended | > event_manipulation | information_schema.character_data | > | | | extended | > event_object_catalog | information_schema.sql_identifier | > | | | extended | > event_object_schema | information_schema.sql_identifier | > | | | extended | > event_object_table | information_schema.sql_identifier | > | | | extended | > action_order | information_schema.cardinal_number | > | | | plain | > action_condition | information_schema.character_data | > | | | extended | > action_statement | information_schema.character_data | > | | | extended | > action_orientation | information_schema.character_data | > | | | extended | > action_timing | information_schema.character_data | > | | | extended | > action_reference_old_table | information_schema.sql_identifier | > | | | extended | > action_reference_new_table | information_schema.sql_identifier | > | | | extended | > action_reference_old_row | information_schema.sql_identifier | > | | | extended | > action_reference_new_row | information_schema.sql_identifier | > | | | extended | > created | information_schema.time_stamp | > | | | plain | > View definition: > 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.oid, c.oid, 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 > PROCEDURE'::text))[1] > ELSE NULL::text > END::information_schema.character_data AS action_condition, > "substring"(pg_get_triggerdef(t.oid), > "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE > PROCEDURE'::text) + 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::character varying::information_schema.sql_identifier AS > action_reference_old_row, > NULL::character varying::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)); > > Look at the line, should "truncate " be here ??? > ( VALUES (4,'INSERT'::text), (8,'DELETE'::text), (16,'UPDATE'::text)) > em(num, text) >
Ashutosh Sharma <ashu.coek88@gmail.com> writes: > This is what I found in the definition of triggers views in > information_schema.sql which says we are intentionally omitting > truncate triggers but then there is no clear cut explanation given for > this omission. The reason is that TRUNCATE triggers aren't in the SQL standard, so they can't be shown by a spec-compliant view. Concretely, SQL:2011 part 11 section 6.62 ("TRIGGERS base table") shows that column as having this definition: EVENT_MANIPULATION INFORMATION_SCHEMA.CHARACTER_DATA CONSTRAINT TRIGGERS_EVENT_MANIPULATION_CHECK CHECK ( EVENT_MANIPULATION IN ( 'INSERT', 'DELETE', 'UPDATE' ) ), Some other DBMSes think it's okay to bend the definition of the information_schema views so that they can show non-spec-compliant objects, but we don't. The point of those views, according to our policy, is to be useful to portable (spec-compliant) applications which presumably will only be creating spec-compliant objects. If somebody makes non-spec-compliant objects in the same database, and we show them in the information_schema, such an application could get mightily confused by what it sees there. In short, if you want to make Postgres-specific objects like ON TRUNCATE triggers, use Postgres-specific system catalog queries to introspect them. regards, tom lane
On Fri, Sep 20, 2019 at 8:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Ashutosh Sharma <ashu.coek88@gmail.com> writes: > > This is what I found in the definition of triggers views in > > information_schema.sql which says we are intentionally omitting > > truncate triggers but then there is no clear cut explanation given for > > this omission. > > The reason is that TRUNCATE triggers aren't in the SQL standard, so > they can't be shown by a spec-compliant view. Concretely, SQL:2011 > part 11 section 6.62 ("TRIGGERS base table") shows that column as > having this definition: > > EVENT_MANIPULATION INFORMATION_SCHEMA.CHARACTER_DATA > CONSTRAINT TRIGGERS_EVENT_MANIPULATION_CHECK > CHECK ( EVENT_MANIPULATION IN > ( 'INSERT', 'DELETE', 'UPDATE' ) ), > > Some other DBMSes think it's okay to bend the definition of the > information_schema views so that they can show non-spec-compliant > objects, but we don't. The point of those views, according to our > policy, is to be useful to portable (spec-compliant) applications > which presumably will only be creating spec-compliant objects. > If somebody makes non-spec-compliant objects in the same database, > and we show them in the information_schema, such an application > could get mightily confused by what it sees there. > > In short, if you want to make Postgres-specific objects like > ON TRUNCATE triggers, use Postgres-specific system catalog > queries to introspect them. > Thanks for the explanation. I could also see this in the postgres docs for CREATE TRIGGER - [1] "The ability to fire triggers for TRUNCATE is a PostgreSQL extension of the SQL standard, as is the ability to define statement-level triggers on views." [1] - https://www.postgresql.org/docs/devel/sql-createtrigger.html > regards, tom lane