PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16015
PG Version11.4
OSlinux
Opened2019-09-20 09:18:30+00
Reported byDamionZ Zhao
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16015
Logged by:          DamionZ Zhao
Email address:      (redacted)
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)

Messages

DateAuthorSubject
2019-09-20 09:18:30+00PG Bug reporting formBUG #16015: information_schema.triggers lack of truncate trigger
2019-09-20 11:12:47+00Ashutosh SharmaRe: BUG #16015: information_schema.triggers lack of truncate trigger
2019-09-20 14:32:37+00Tom LaneRe: BUG #16015: information_schema.triggers lack of truncate trigger
2019-09-20 14:57:37+00Ashutosh SharmaRe: BUG #16015: information_schema.triggers lack of truncate trigger