Hello. I wanted to know what it would take to implement triggers on foreign tables. It seems that statement-level triggers can work provided they are allowed in the code.
Please find attached a simple POC patch that implement just that. For row-level triggers, it seems more complicated. From what I understand, OLD/NEW tuples are fetched from the heap using their ctid (except for BEFORE INSERT triggers). How could this be adapted for foreign tables ? -- Ronan Dunklau
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index e5ec738..08d133c 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -93,7 +93,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
<para>
The following table summarizes which types of triggers may be used on
- tables and views:
+ tables, views and foreign tables:
</para>
<informaltable id="supported-trigger-types">
@@ -111,7 +111,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
<entry align="center" morerows="1"><literal>BEFORE</></entry>
<entry align="center"><command>INSERT</>/<command>UPDATE</>/<command>DELETE</></entry>
<entry align="center">Tables</entry>
- <entry align="center">Tables and views</entry>
+ <entry align="center">Tables, views and foreign tables</entry>
</row>
<row>
<entry align="center"><command>TRUNCATE</></entry>
@@ -122,7 +122,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
<entry align="center" morerows="1"><literal>AFTER</></entry>
<entry align="center"><command>INSERT</>/<command>UPDATE</>/<command>DELETE</></entry>
<entry align="center">Tables</entry>
- <entry align="center">Tables and views</entry>
+ <entry align="center">Tables, views and foreign tables</entry>
</row>
<row>
<entry align="center"><command>TRUNCATE</></entry>
@@ -244,7 +244,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the table or view the trigger
+ The name (optionally schema-qualified) of the table, view or foreign table the trigger
is for.
</para>
</listitem>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index f579340..37c0a35 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -33,7 +33,7 @@
<para>
A trigger is a specification that the database should automatically
execute a particular function whenever a certain type of operation is
- performed. Triggers can be attached to both tables and views.
+ performed. Triggers can be attached to tables, views and foreign tables.
</para>
<para>
@@ -64,6 +64,15 @@
</para>
<para>
+ On foreign tables, trigger can be defined to execute either before or after any
+ <command>INSERT</command>,
+ <command>UPDATE</command> or
+ <command>DELETE</command> operation, only onece per <acronym>SQL statement.
+ This means that row-level triggers are not supported for foreign tables.
+ </para>
+
+
+ <para>
The trigger function must be defined before the trigger itself can be
created. The trigger function must be declared as a
function taking no arguments and returning type <literal>trigger</>.
@@ -96,6 +105,7 @@
after may only be defined at statement level, while triggers that fire
instead of an <command>INSERT</command>, <command>UPDATE</command>,
or <command>DELETE</command> may only be defined at row level.
+ On foreign tables, triggers can not be defined at row level.
</para>
<para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index adc74dd..68bfa9c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -3141,13 +3141,16 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_EnableTrig: /* ENABLE TRIGGER variants */
- case AT_EnableAlwaysTrig:
- case AT_EnableReplicaTrig:
case AT_EnableTrigAll:
case AT_EnableTrigUser:
case AT_DisableTrig: /* DISABLE TRIGGER variants */
case AT_DisableTrigAll:
case AT_DisableTrigUser:
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ pass = AT_PASS_MISC;
+ break;
+ case AT_EnableReplicaTrig:
+ case AT_EnableAlwaysTrig:
case AT_EnableRule: /* ENABLE/DISABLE RULE variants */
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index d86e9ad..d84b61b 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -184,12 +184,23 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
RelationGetRelationName(rel)),
errdetail("Views cannot have TRUNCATE triggers.")));
}
- else
+ else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ if(stmt->row){
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is a foreign table",
+ RelationGetRelationName(rel)),
+ errdetail("Foreign tables cannot have row-level triggers.")));
+
+ }
+ }
+ else {
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("\"%s\" is not a table or view",
RelationGetRelationName(rel))));
-
+ }
if (!allowSystemTableMods && IsSystemRelation(rel))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -1062,10 +1073,11 @@ RemoveTriggerById(Oid trigOid)
rel = heap_open(relid, AccessExclusiveLock);
if (rel->rd_rel->relkind != RELKIND_RELATION &&
- rel->rd_rel->relkind != RELKIND_VIEW)
+ rel->rd_rel->relkind != RELKIND_VIEW &&
+ rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table or view",
+ errmsg("\"%s\" is not a table, view or foreign table",
RelationGetRelationName(rel))));
if (!allowSystemTableMods && IsSystemRelation(rel))
@@ -1166,10 +1178,11 @@ RangeVarCallbackForRenameTrigger(const RangeVar *rv, Oid relid, Oid oldrelid,
form = (Form_pg_class) GETSTRUCT(tuple);
/* only tables and views can have triggers */
- if (form->relkind != RELKIND_RELATION && form->relkind != RELKIND_VIEW)
+ if (form->relkind != RELKIND_RELATION && form->relkind != RELKIND_VIEW &&
+ form->relkind != RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table or view", rv->relname)));
+ errmsg("\"%s\" is not a table, view or foreign table", rv->relname)));
/* you must own the table to rename one of its triggers */
if (!pg_class_ownercheck(relid, GetUserId()))
@@ -1846,7 +1859,6 @@ ExecCallTriggerFunc(TriggerData *trigdata,
InvalidOid, (Node *) trigdata, NULL);
pgstat_init_function_usage(&fcinfo, &fcusage);
-
MyTriggerDepth++;
PG_TRY();
{
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 60506e0..9d0ba8b 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1158,6 +1158,24 @@ CREATE USER MAPPING FOR current_user SERVER s9;
DROP SERVER s9 CASCADE; -- ERROR
ERROR: must be owner of foreign server s9
RESET ROLE;
+-- Triggers
+CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_trigger(); -- ERROR
+ERROR: "foreign_table_1" is a foreign table
+DETAIL: Foreign tables cannot have row-level triggers.
+CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1
+FOR EACH STATEMENT
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DISABLE TRIGGER trigtest;
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ENABLE TRIGGER trigtest;
+DROP TRIGGER trigtest ON foreign_schema.foreign_table_1;
+DROP FUNCTION dummy_trigger();
-- DROP FOREIGN TABLE
DROP FOREIGN TABLE no_table; -- ERROR
ERROR: foreign table "no_table" does not exist
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index f819eb1..74e6a40 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -470,6 +470,29 @@ CREATE USER MAPPING FOR current_user SERVER s9;
DROP SERVER s9 CASCADE; -- ERROR
RESET ROLE;
+-- Triggers
+CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+
+CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1
+FOR EACH ROW
+EXECUTE PROCEDURE dummy_trigger(); -- ERROR
+
+CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1
+FOR EACH STATEMENT
+EXECUTE PROCEDURE dummy_trigger();
+
+
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DISABLE TRIGGER trigtest;
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ENABLE TRIGGER trigtest;
+
+DROP TRIGGER trigtest ON foreign_schema.foreign_table_1;
+
+DROP FUNCTION dummy_trigger();
+
-- DROP FOREIGN TABLE
DROP FOREIGN TABLE no_table; -- ERROR
DROP FOREIGN TABLE IF EXISTS no_table;
signature.asc
Description: This is a digitally signed message part.
