Re: BUG #16015: information_schema.triggers lack of truncate trigger

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(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 16015
> Logged by: DamionZ Zhao
> Email address: zhq651(at)126(dot)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)
>