Skip to content

Commit 3b00a94

Browse files
MasaoFujiiMasaoFujii
committed
Support TRUNCATE triggers on foreign tables.
Now some foreign data wrappers support TRUNCATE command. So it's useful to support TRUNCATE triggers on foreign tables for audit logging or for preventing undesired truncation. Author: Yugo Nagata Reviewed-by: Fujii Masao, Ian Lawrence Barwick Discussion: https://postgr.es/m/[email protected]
1 parent 14168d3 commit 3b00a94

File tree

4 files changed

+11
-14
lines changed

4 files changed

+11
-14
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6732,9 +6732,9 @@ BEGIN
67326732
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
67336733
RETURN NULL;
67346734
END;$$;
6735-
CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
6735+
CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
67366736
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
6737-
CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
6737+
CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
67386738
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
67396739
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
67406740
LANGUAGE plpgsql AS $$
@@ -6821,6 +6821,9 @@ NOTICE: OLD: (1,update),NEW: (1,updateupdate)
68216821
NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1
68226822
NOTICE: OLD: (1,update),NEW: (1,updateupdate)
68236823
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER, level = STATEMENT
6824+
truncate rem1;
6825+
NOTICE: trigger_func(<NULL>) called: action = TRUNCATE, when = BEFORE, level = STATEMENT
6826+
NOTICE: trigger_func(<NULL>) called: action = TRUNCATE, when = AFTER, level = STATEMENT
68246827
-- cleanup
68256828
DROP TRIGGER trig_row_before ON rem1;
68266829
DROP TRIGGER trig_row_after ON rem1;
@@ -7087,7 +7090,7 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
70877090
NOTICE: NEW: (13,"test triggered !")
70887091
ctid
70897092
--------
7090-
(0,32)
7093+
(0,25)
70917094
(1 row)
70927095

70937096
-- cleanup

‎contrib/postgres_fdw/sql/postgres_fdw.sql‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1595,9 +1595,9 @@ BEGIN
15951595
RETURN NULL;
15961596
END;$$;
15971597

1598-
CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
1598+
CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
15991599
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
1600-
CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
1600+
CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
16011601
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
16021602

16031603
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
@@ -1652,6 +1652,7 @@ delete from rem1;
16521652
insert into rem1 values(1,'insert');
16531653
update rem1 set f2 = 'update' where f1 = 1;
16541654
update rem1 set f2 = f2 || f2;
1655+
truncate rem1;
16551656

16561657

16571658
-- cleanup

‎doc/src/sgml/ref/create_trigger.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -131,7 +131,7 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
131131
<row>
132132
<entry align="center"><command>TRUNCATE</command></entry>
133133
<entry align="center">&mdash;</entry>
134-
<entry align="center">Tables</entry>
134+
<entry align="center">Tables and foreign tables</entry>
135135
</row>
136136
<row>
137137
<entry align="center" morerows="1"><literal>AFTER</literal></entry>
@@ -142,7 +142,7 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
142142
<row>
143143
<entry align="center"><command>TRUNCATE</command></entry>
144144
<entry align="center">&mdash;</entry>
145-
<entry align="center">Tables</entry>
145+
<entry align="center">Tables and foreign tables</entry>
146146
</row>
147147
<row>
148148
<entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry>

‎src/backend/commands/trigger.c‎

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -295,13 +295,6 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
295295
RelationGetRelationName(rel)),
296296
errdetail("Foreign tables cannot have INSTEAD OF triggers.")));
297297

298-
if (TRIGGER_FOR_TRUNCATE(stmt->events))
299-
ereport(ERROR,
300-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
301-
errmsg("\"%s\" is a foreign table",
302-
RelationGetRelationName(rel)),
303-
errdetail("Foreign tables cannot have TRUNCATE triggers.")));
304-
305298
/*
306299
* We disallow constraint triggers to protect the assumption that
307300
* triggers on FKs can't be deferred. See notes with AfterTriggers

0 commit comments

Comments
 (0)