Hello Fujii-san,
Thank you for reviewing the patch!
On Fri, 8 Jul 2022 00:54:37 +0900
Fujii Masao <masao.fu...@oss.nttdata.com> wrote:
>
>
> On 2022/06/30 19:38, Yugo NAGATA wrote:
> > Hello,
> >
> > I propose supporting TRUNCATE triggers on foreign tables
> > because some FDW now supports TRUNCATE. I think such triggers
> > are useful for audit logging or for preventing undesired
> > truncate.
> >
> > Patch attached.
>
> Thanks for the patch! It looks good to me except the following thing.
>
> <entry align="center"><command>TRUNCATE</command></entry>
> <entry align="center">—</entry>
> - <entry align="center">Tables</entry>
> + <entry align="center">Tables and foreign tables</entry>
> </row>
>
> You added "foreign tables" for BEFORE statement-level trigger as the above,
> but ISTM that you also needs to do that for AFTER statement-level trigger. No?
Oops, I forgot it. I attached the updated patch.
Regards,
Yugo Nagata
>
> Regards,
>
> --
> Fujii Masao
> Advanced Computing Technology Center
> Research and Development Headquarters
> NTT DATA CORPORATION
--
Yugo NAGATA <nag...@sraoss.co.jp>
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 44457f930c..5f2ef88cf3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6732,9 +6732,9 @@ BEGIN
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;$$;
-CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
+CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
-CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
+CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
LANGUAGE plpgsql AS $$
@@ -6821,6 +6821,9 @@ NOTICE: OLD: (1,update),NEW: (1,updateupdate)
NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1
NOTICE: OLD: (1,update),NEW: (1,updateupdate)
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER, level = STATEMENT
+truncate rem1;
+NOTICE: trigger_func(<NULL>) called: action = TRUNCATE, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(<NULL>) called: action = TRUNCATE, when = AFTER, level = STATEMENT
-- cleanup
DROP TRIGGER trig_row_before ON rem1;
DROP TRIGGER trig_row_after ON rem1;
@@ -7087,7 +7090,7 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
NOTICE: NEW: (13,"test triggered !")
ctid
--------
- (0,32)
+ (0,25)
(1 row)
-- cleanup
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 92d1212027..ae1fc8f58b 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1595,9 +1595,9 @@ BEGIN
RETURN NULL;
END;$$;
-CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
+CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
-CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
+CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
@@ -1652,6 +1652,7 @@ delete from rem1;
insert into rem1 values(1,'insert');
update rem1 set f2 = 'update' where f1 = 1;
update rem1 set f2 = f2 || f2;
+truncate rem1;
-- cleanup
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index ee42f413e9..982ab6f3ee 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -131,7 +131,7 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
<row>
<entry align="center"><command>TRUNCATE</command></entry>
<entry align="center">—</entry>
- <entry align="center">Tables</entry>
+ <entry align="center">Tables and foreign tables</entry>
</row>
<row>
<entry align="center" morerows="1"><literal>AFTER</literal></entry>
@@ -142,7 +142,7 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
<row>
<entry align="center"><command>TRUNCATE</command></entry>
<entry align="center">—</entry>
- <entry align="center">Tables</entry>
+ <entry align="center">Tables and foreign tables</entry>
</row>
<row>
<entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry>
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 13cb516752..b8db53b66d 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -295,13 +295,6 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
RelationGetRelationName(rel)),
errdetail("Foreign tables cannot have INSTEAD OF triggers.")));
- if (TRIGGER_FOR_TRUNCATE(stmt->events))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a foreign table",
- RelationGetRelationName(rel)),
- errdetail("Foreign tables cannot have TRUNCATE triggers.")));
-
/*
* We disallow constraint triggers to protect the assumption that
* triggers on FKs can't be deferred. See notes with AfterTriggers
