Re: BUG #16015: information_schema.triggers lack of truncate trigger - Mailing list pgsql-bugs
From | Ashutosh Sharma |
---|---|
Subject | Re: BUG #16015: information_schema.triggers lack of truncate trigger |
Date | |
Msg-id | CAE9k0Pnd_M-8-gk=67M35mT7PQBWA2Qase76T5pQiRrLob1v6w@mail.gmail.com Whole thread Raw |
In response to | BUG #16015: information_schema.triggers lack of truncate trigger (PG Bug reporting form <noreply@postgresql.org>) |
Responses |
Re: BUG #16015: information_schema.triggers lack of truncate trigger
|
List | pgsql-bugs |
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) >
pgsql-bugs by date: