Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 3b00a94

Browse filesBrowse 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/20220630193848.5b02e0d6076b86617a915682@sraoss.co.jp
1 parent 14168d3 commit 3b00a94
Copy full SHA for 3b00a94

File tree

4 files changed

+11
-14
lines changed
Filter options

4 files changed

+11
-14
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/expected/postgres_fdw.out
+6-3Lines 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

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/sql/postgres_fdw.sql
+3-2Lines 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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_trigger.sgml
+2-2Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/trigger.c
-7Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.