Обсуждение: 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