BUG #16015: information_schema.triggers lack of truncate trigger
От | PG Bug reporting form |
---|---|
Тема | BUG #16015: information_schema.triggers lack of truncate trigger |
Дата | |
Msg-id | 16015-9cbd6cdd035d96f4@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16015: information_schema.triggers lack of truncate trigger
|
Список | pgsql-bugs |
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 по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: BUG #16014: how to modify column ev_action of pg_rewrite
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #16016: deadlock with startup process, AccessExclusiveLock on pg_statistic's toast table