From 9e6104c6672dc948a430d1ee269b0c31bf5bc974 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 9 May 2017 23:34:02 -0400 Subject: [PATCH] Prohibit transition tables on views and foreign tables. Thomas Munro, per off-list report from Prabhat Sabu. Changes to the message wording for consistency with the existing relkind check for partitioned tables by me. Discussion: http://postgr.es/m/CAEepm=2xJFFpGM+N=gpWx-9Nft2q1oaFZX07_y23AHCrJQLt0g@mail.gmail.com --- src/backend/commands/trigger.c | 14 ++++++++++++++ src/test/regress/expected/foreign_data.out | 7 +++++++ src/test/regress/expected/triggers.out | 15 +++++++++++++++ src/test/regress/sql/foreign_data.sql | 6 ++++++ src/test/regress/sql/triggers.sql | 15 +++++++++++++++ 5 files changed, 57 insertions(+) diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index c0511639db..819395a967 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -361,6 +361,20 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, RelationGetRelationName(rel)), errdetail("Triggers on partitioned tables cannot have transition tables."))); + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a foreign table", + RelationGetRelationName(rel)), + errdetail("Triggers on foreign tables cannot have transition tables."))); + + if (rel->rd_rel->relkind == RELKIND_VIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a view", + RelationGetRelationName(rel)), + errdetail("Triggers on views cannot have transition tables."))); + if (stmt->timing != TRIGGER_TYPE_AFTER) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index af327d030d..6a1f22ebeb 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1269,6 +1269,13 @@ CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH STATEMENT EXECUTE PROCEDURE dummy_trigger(); +CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR +ON foreign_schema.foreign_table_1 +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT +EXECUTE PROCEDURE dummy_trigger(); +ERROR: "foreign_table_1" is a foreign table +DETAIL: Triggers on foreign tables cannot have transition tables. CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH ROW diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 10a301310b..c300449f3a 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1785,9 +1785,24 @@ drop trigger my_trigger on my_table_42; create trigger my_trigger after update on my_table_42 referencing old table as old_table for each statement execute procedure my_trigger_function(); drop trigger my_trigger on my_table_42; +drop function my_trigger_function(); drop table my_table_42; drop table my_table; -- +-- Verify that triggers with transition tables are not allowed on +-- views +-- +create table my_table (i int); +create view my_view as select * from my_table; +create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; +create trigger my_trigger after update on my_view referencing old table as old_table + for each statement execute procedure my_trigger_function(); +ERROR: "my_view" is a view +DETAIL: Triggers on views cannot have transition tables. +drop function my_trigger_function(); +drop view my_view; +drop table my_table; +-- -- Verify that per-statement triggers are fired for partitioned tables -- create table parted_stmt_trig (a int) partition by list (a); diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index ba528b5d36..49255e309c 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -527,6 +527,12 @@ ON foreign_schema.foreign_table_1 FOR EACH STATEMENT EXECUTE PROCEDURE dummy_trigger(); +CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR +ON foreign_schema.foreign_table_1 +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT +EXECUTE PROCEDURE dummy_trigger(); + CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH ROW diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 84b5ada554..e5dbcaeea3 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1261,9 +1261,24 @@ drop trigger my_trigger on my_table_42; create trigger my_trigger after update on my_table_42 referencing old table as old_table for each statement execute procedure my_trigger_function(); drop trigger my_trigger on my_table_42; +drop function my_trigger_function(); drop table my_table_42; drop table my_table; +-- +-- Verify that triggers with transition tables are not allowed on +-- views +-- + +create table my_table (i int); +create view my_view as select * from my_table; +create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; +create trigger my_trigger after update on my_view referencing old table as old_table + for each statement execute procedure my_trigger_function(); +drop function my_trigger_function(); +drop view my_view; +drop table my_table; + -- -- Verify that per-statement triggers are fired for partitioned tables -- -- 2.40.0