From 59702716324ab9c07b02fb005dcf14c7f48c4632 Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Fri, 31 Mar 2017 23:30:08 -0500 Subject: [PATCH] Add transition table support to plpgsql. Kevin Grittner and Thomas Munro Reviewed by Heikki Linnakangas, David Fetter, and Thomas Munro with valuable comments and suggestions from many others --- doc/src/sgml/ref/create_trigger.sgml | 5 + src/pl/plpgsql/src/pl_comp.c | 13 +- src/pl/plpgsql/src/pl_exec.c | 47 ++++ src/pl/plpgsql/src/plpgsql.h | 14 +- src/test/regress/expected/plpgsql.out | 287 +++++++++++++++++++++++++ src/test/regress/expected/triggers.out | 24 +++ src/test/regress/sql/plpgsql.sql | 283 ++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 23 ++ 8 files changed, 685 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index c9a9fa6ba2..e22e42e7dc 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -322,6 +322,11 @@ UPDATE OF column_name1 [, column_name2 The (unqualified) name to be used within the trigger for this relation. + + + So far only triggers written in C or PL/pgSQL support this. + + diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index bed343ea0c..a6375511f6 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -589,11 +589,11 @@ do_compile(FunctionCallInfo fcinfo, errmsg("trigger functions cannot have declared arguments"), errhint("The arguments of the trigger can be accessed through TG_NARGS and TG_ARGV instead."))); - /* Add the record for referencing NEW */ + /* Add the record for referencing NEW ROW */ rec = plpgsql_build_record("new", 0, true); function->new_varno = rec->dno; - /* Add the record for referencing OLD */ + /* Add the record for referencing OLD ROW */ rec = plpgsql_build_record("old", 0, true); function->old_varno = rec->dno; @@ -2453,15 +2453,16 @@ compute_function_hashkey(FunctionCallInfo fcinfo, hashkey->isTrigger = CALLED_AS_TRIGGER(fcinfo); /* - * if trigger, get relation OID. In validation mode we do not know what - * relation is intended to be used, so we leave trigrelOid zero; the hash - * entry built in this case will never really be used. + * if trigger, get its OID. In validation mode we do not know what + * relation or transition table names are intended to be used, so we leave + * trigOid zero; the hash entry built in this case will never really be + * used. */ if (hashkey->isTrigger && !forValidator) { TriggerData *trigdata = (TriggerData *) fcinfo->context; - hashkey->trigrelOid = RelationGetRelid(trigdata->tg_relation); + hashkey->trigOid = trigdata->tg_trigger->tgoid; } /* get input collation, if known */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index c27935b51b..43da986fc0 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -689,6 +689,47 @@ plpgsql_exec_trigger(PLpgSQL_function *func, else elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, or UPDATE"); + /* + * Capture the NEW and OLD transition TABLE tuplestores (if specified for + * this trigger). + */ + if (trigdata->tg_newtable || trigdata->tg_oldtable) + { + estate.queryEnv = create_queryEnv(); + if (trigdata->tg_newtable) + { + EphemeralNamedRelation enr = + palloc(sizeof(EphemeralNamedRelationData)); + int rc PG_USED_FOR_ASSERTS_ONLY; + + enr->md.name = trigdata->tg_trigger->tgnewtable; + enr->md.reliddesc = RelationGetRelid(trigdata->tg_relation); + enr->md.tupdesc = NULL; + enr->md.enrtype = ENR_NAMED_TUPLESTORE; + enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_newtable); + enr->reldata = trigdata->tg_newtable; + register_ENR(estate.queryEnv, enr); + rc = SPI_register_relation(enr); + Assert(rc >= 0); + } + if (trigdata->tg_oldtable) + { + EphemeralNamedRelation enr = + palloc(sizeof(EphemeralNamedRelationData)); + int rc PG_USED_FOR_ASSERTS_ONLY; + + enr->md.name = trigdata->tg_trigger->tgoldtable; + enr->md.reliddesc = RelationGetRelid(trigdata->tg_relation); + enr->md.tupdesc = NULL; + enr->md.enrtype = ENR_NAMED_TUPLESTORE; + enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_oldtable); + enr->reldata = trigdata->tg_oldtable; + register_ENR(estate.queryEnv, enr); + rc = SPI_register_relation(enr); + Assert(rc >= 0); + } + } + /* * Assign the special tg_ variables */ @@ -3442,6 +3483,9 @@ plpgsql_estate_setup(PLpgSQL_execstate *estate, estate->paramLI->paramMask = NULL; estate->params_dirty = false; + /* default tuplestore cache to "none" */ + estate->queryEnv = NULL; + /* set up for use of appropriate simple-expression EState and cast hash */ if (simple_eval_estate) { @@ -7329,6 +7373,9 @@ exec_dynquery_with_params(PLpgSQL_execstate *estate, /* Release transient data */ MemoryContextReset(stmt_mcontext); + /* Make sure the portal knows about any named tuplestores. */ + portal->queryEnv = estate->queryEnv; + return portal; } diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index b7e103b514..43a62ef34e 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -20,6 +20,7 @@ #include "commands/event_trigger.h" #include "commands/trigger.h" #include "executor/spi.h" +#include "utils/queryenvironment.h" /********************************************************************** * Definitions @@ -780,12 +781,12 @@ typedef struct PLpgSQL_func_hashkey /* be careful that pad bytes in this struct get zeroed! */ /* - * For a trigger function, the OID of the relation triggered on is part of - * the hash key --- we want to compile the trigger separately for each - * relation it is used with, in case the rowtype is different. Zero if - * not called as a trigger. + * For a trigger function, the OID of the trigger is part of the hash key + * --- we want to compile the trigger function separately for each trigger + * it is used with, in case the rowtype or transition table names are + * different. Zero if not called as a trigger. */ - Oid trigrelOid; + Oid trigOid; /* * We must include the input collation as part of the hash key too, @@ -910,6 +911,9 @@ typedef struct PLpgSQL_execstate ParamListInfo paramLI; bool params_dirty; /* T if any resettable datum has been passed */ + /* custom environment for parsing/execution of query for this context */ + QueryEnvironment *queryEnv; + /* EState to use for "simple" expression evaluation */ EState *simple_eval_estate; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 04848c10a2..93b71c7c78 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -5684,3 +5684,290 @@ end; $$; ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check" CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment +-- +-- test usage of transition tables in AFTER triggers +-- +CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); +CREATE FUNCTION transition_table_base_ins_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM newtable + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); +ERROR: OLD TABLE can only be specified for a DELETE or UPDATE trigger +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); +INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); +INFO: Named Tuplestore Scan + Output: id, val + +INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); +INFO: Named Tuplestore Scan + Output: id, val + +CREATE OR REPLACE FUNCTION transition_table_base_upd_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; +CREATE TRIGGER transition_table_base_upd_trig + AFTER UPDATE ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_upd_func(); +UPDATE transition_table_base + SET val = '*' || val || '*' + WHERE id BETWEEN 2 AND 3; +INFO: Hash Full Join + Output: COALESCE(ot.id, nt.id), ot.val, nt.val + Hash Cond: (ot.id = nt.id) + -> Named Tuplestore Scan + Output: ot.id, ot.val + -> Hash + Output: nt.id, nt.val + -> Named Tuplestore Scan + Output: nt.id, nt.val + +CREATE TABLE transition_table_level1 +( + level1_no serial NOT NULL , + level1_node_name varchar(255), + PRIMARY KEY (level1_no) +) WITHOUT OIDS; +CREATE TABLE transition_table_level2 +( + level2_no serial NOT NULL , + parent_no int NOT NULL, + level1_node_name varchar(255), + PRIMARY KEY (level2_no) +) WITHOUT OIDS; +CREATE TABLE transition_table_status +( + level int NOT NULL, + node_no int NOT NULL, + status int, + PRIMARY KEY (level, node_no) +) WITHOUT OIDS; +CREATE FUNCTION transition_table_level1_ri_parent_del_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE n bigint; + BEGIN + PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level1_ri_parent_del_trigger + AFTER DELETE ON transition_table_level1 + REFERENCING OLD TABLE AS p + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_del_func(); +CREATE FUNCTION transition_table_level1_ri_parent_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE + x int; + BEGIN + WITH p AS (SELECT level1_no, sum(delta) cnt + FROM (SELECT level1_no, 1 AS delta FROM i + UNION ALL + SELECT level1_no, -1 AS delta FROM d) w + GROUP BY level1_no + HAVING sum(delta) < 0) + SELECT level1_no + FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no + INTO x; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger + AFTER UPDATE ON transition_table_level1 + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_upd_func(); +CREATE FUNCTION transition_table_level2_ri_child_insupd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + PERFORM FROM i + LEFT JOIN transition_table_level1 p + ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no + WHERE p.level1_no IS NULL; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level2_ri_child_insupd_trigger + AFTER INSERT OR UPDATE ON transition_table_level2 + REFERENCING NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_ri_child_insupd_func(); +-- create initial test data +INSERT INTO transition_table_level1 (level1_no) + SELECT generate_series(1,200); +ANALYZE transition_table_level1; +INSERT INTO transition_table_level2 (level2_no, parent_no) + SELECT level2_no, level2_no / 50 + 1 AS parent_no + FROM generate_series(1,9999) level2_no; +ANALYZE transition_table_level2; +INSERT INTO transition_table_status (level, node_no, status) + SELECT 1, level1_no, 0 FROM transition_table_level1; +INSERT INTO transition_table_status (level, node_no, status) + SELECT 2, level2_no, 0 FROM transition_table_level2; +ANALYZE transition_table_status; +INSERT INTO transition_table_level1(level1_no) + SELECT generate_series(201,1000); +ANALYZE transition_table_level1; +-- behave reasonably if someone tries to modify a transition table +CREATE FUNCTION transition_table_level2_bad_usage_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + INSERT INTO d VALUES (1000000, 1000000, 'x'); + RETURN NULL; + END; +$$; +CREATE TRIGGER transition_table_level2_bad_usage_trigger + AFTER DELETE ON transition_table_level2 + REFERENCING OLD TABLE AS d + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_bad_usage_func(); +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 301 AND 305; +ERROR: relation "d" cannot be the target of a modifying statement +CONTEXT: SQL statement "INSERT INTO d VALUES (1000000, 1000000, 'x')" +PL/pgSQL function transition_table_level2_bad_usage_func() line 3 at SQL statement +DROP TRIGGER transition_table_level2_bad_usage_trigger + ON transition_table_level2; +-- attempt modifications which would break RI (should all fail) +DELETE FROM transition_table_level1 + WHERE level1_no = 25; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_del_func() line 6 at RAISE +UPDATE transition_table_level1 SET level1_no = -1 + WHERE level1_no = 30; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level1_ri_parent_upd_func() line 15 at RAISE +INSERT INTO transition_table_level2 (level2_no, parent_no) + VALUES (10000, 10000); +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE +UPDATE transition_table_level2 SET parent_no = 2000 + WHERE level2_no = 40; +ERROR: RI error +CONTEXT: PL/pgSQL function transition_table_level2_ri_child_insupd_func() line 8 at RAISE +-- attempt modifications which would not break RI (should all succeed) +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 201 AND 1000; +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 100000000 AND 100000010; +SELECT count(*) FROM transition_table_level1; + count +------- + 200 +(1 row) + +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 211 AND 220; +SELECT count(*) FROM transition_table_level2; + count +------- + 9989 +(1 row) + +CREATE TABLE alter_table_under_transition_tables +( + id int PRIMARY KEY, + name text +); +CREATE FUNCTION alter_table_under_transition_tables_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + RAISE WARNING 'old table = %, new table = %', + (SELECT string_agg(id || '=' || name, ',') FROM d), + (SELECT string_agg(id || '=' || name, ',') FROM i); + RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); + RETURN NULL; +END; +$$; +CREATE TRIGGER alter_table_under_transition_tables_upd_trigger + AFTER UPDATE ON alter_table_under_transition_tables + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + alter_table_under_transition_tables_upd_func(); +INSERT INTO alter_table_under_transition_tables + VALUES (1, '1'), (2, '2'), (3, '3'); +UPDATE alter_table_under_transition_tables + SET name = name || name; +WARNING: old table = 1=1,2=2,3=3, new table = 1=11,2=22,3=33 +NOTICE: one = 1 +-- now change 'name' to an integer to see what happens... +ALTER TABLE alter_table_under_transition_tables + ALTER COLUMN name TYPE int USING name::integer; +UPDATE alter_table_under_transition_tables + SET name = (name::text || name::text)::integer; +WARNING: old table = 1=11,2=22,3=33, new table = 1=1111,2=2222,3=3333 +NOTICE: one = 1 +-- now drop column 'name' +ALTER TABLE alter_table_under_transition_tables + DROP column name; +UPDATE alter_table_under_transition_tables + SET id = id; +ERROR: column "name" does not exist +LINE 1: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d) + ^ +QUERY: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d) +CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index f408475f33..4b0b3b7c42 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1763,3 +1763,27 @@ select * from upsert; drop table upsert; drop function upsert_before_func(); drop function upsert_after_func(); +-- +-- Verify that triggers are prevented on partitioned tables if they would +-- access row data (ROW and STATEMENT-with-transition-table) +-- +create table my_table (i int) partition by list (i); +create table my_table_42 partition of my_table for values in (42); +create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; +create trigger my_trigger before update on my_table for each row execute procedure my_trigger_function(); +ERROR: "my_table" is a partitioned table +DETAIL: Partitioned tables cannot have ROW triggers. +create trigger my_trigger after update on my_table referencing old table as old_table + for each statement execute procedure my_trigger_function(); +ERROR: "my_table" is a partitioned table +DETAIL: Triggers on partitioned tables cannot have transition tables. +-- +-- Verify that triggers are allowed on partitions +-- +create trigger my_trigger before update on my_table_42 for each row execute procedure my_trigger_function(); +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 table my_table_42; +drop table my_table; diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 31dcbdffdd..628a9d126e 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -4475,3 +4475,286 @@ begin v_test := 0 || v_test; -- fail end; $$; + +-- +-- test usage of transition tables in AFTER triggers +-- + +CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); + +CREATE FUNCTION transition_table_base_ins_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM newtable + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; + +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); + +CREATE TRIGGER transition_table_base_ins_trig + AFTER INSERT ON transition_table_base + REFERENCING NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_ins_func(); + +INSERT INTO transition_table_base VALUES (1, 'One'), (2, 'Two'); +INSERT INTO transition_table_base VALUES (3, 'Three'), (4, 'Four'); + +CREATE OR REPLACE FUNCTION transition_table_base_upd_func() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +DECLARE + t text; + l text; +BEGIN + t = ''; + FOR l IN EXECUTE + $q$ + EXPLAIN (TIMING off, COSTS off, VERBOSE on) + SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) + $q$ LOOP + t = t || l || E'\n'; + END LOOP; + + RAISE INFO '%', t; + RETURN new; +END; +$$; + +CREATE TRIGGER transition_table_base_upd_trig + AFTER UPDATE ON transition_table_base + REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable + FOR EACH STATEMENT + EXECUTE PROCEDURE transition_table_base_upd_func(); + +UPDATE transition_table_base + SET val = '*' || val || '*' + WHERE id BETWEEN 2 AND 3; + +CREATE TABLE transition_table_level1 +( + level1_no serial NOT NULL , + level1_node_name varchar(255), + PRIMARY KEY (level1_no) +) WITHOUT OIDS; + +CREATE TABLE transition_table_level2 +( + level2_no serial NOT NULL , + parent_no int NOT NULL, + level1_node_name varchar(255), + PRIMARY KEY (level2_no) +) WITHOUT OIDS; + +CREATE TABLE transition_table_status +( + level int NOT NULL, + node_no int NOT NULL, + status int, + PRIMARY KEY (level, node_no) +) WITHOUT OIDS; + +CREATE FUNCTION transition_table_level1_ri_parent_del_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE n bigint; + BEGIN + PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; + +CREATE TRIGGER transition_table_level1_ri_parent_del_trigger + AFTER DELETE ON transition_table_level1 + REFERENCING OLD TABLE AS p + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_del_func(); + +CREATE FUNCTION transition_table_level1_ri_parent_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + DECLARE + x int; + BEGIN + WITH p AS (SELECT level1_no, sum(delta) cnt + FROM (SELECT level1_no, 1 AS delta FROM i + UNION ALL + SELECT level1_no, -1 AS delta FROM d) w + GROUP BY level1_no + HAVING sum(delta) < 0) + SELECT level1_no + FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no + INTO x; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; + +CREATE TRIGGER transition_table_level1_ri_parent_upd_trigger + AFTER UPDATE ON transition_table_level1 + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level1_ri_parent_upd_func(); + +CREATE FUNCTION transition_table_level2_ri_child_insupd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + PERFORM FROM i + LEFT JOIN transition_table_level1 p + ON p.level1_no IS NOT NULL AND p.level1_no = i.parent_no + WHERE p.level1_no IS NULL; + IF FOUND THEN + RAISE EXCEPTION 'RI error'; + END IF; + RETURN NULL; + END; +$$; + +CREATE TRIGGER transition_table_level2_ri_child_insupd_trigger + AFTER INSERT OR UPDATE ON transition_table_level2 + REFERENCING NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_ri_child_insupd_func(); + +-- create initial test data +INSERT INTO transition_table_level1 (level1_no) + SELECT generate_series(1,200); +ANALYZE transition_table_level1; + +INSERT INTO transition_table_level2 (level2_no, parent_no) + SELECT level2_no, level2_no / 50 + 1 AS parent_no + FROM generate_series(1,9999) level2_no; +ANALYZE transition_table_level2; + +INSERT INTO transition_table_status (level, node_no, status) + SELECT 1, level1_no, 0 FROM transition_table_level1; + +INSERT INTO transition_table_status (level, node_no, status) + SELECT 2, level2_no, 0 FROM transition_table_level2; +ANALYZE transition_table_status; + +INSERT INTO transition_table_level1(level1_no) + SELECT generate_series(201,1000); +ANALYZE transition_table_level1; + +-- behave reasonably if someone tries to modify a transition table +CREATE FUNCTION transition_table_level2_bad_usage_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ + BEGIN + INSERT INTO d VALUES (1000000, 1000000, 'x'); + RETURN NULL; + END; +$$; + +CREATE TRIGGER transition_table_level2_bad_usage_trigger + AFTER DELETE ON transition_table_level2 + REFERENCING OLD TABLE AS d + FOR EACH STATEMENT EXECUTE PROCEDURE + transition_table_level2_bad_usage_func(); + +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 301 AND 305; + +DROP TRIGGER transition_table_level2_bad_usage_trigger + ON transition_table_level2; + +-- attempt modifications which would break RI (should all fail) +DELETE FROM transition_table_level1 + WHERE level1_no = 25; + +UPDATE transition_table_level1 SET level1_no = -1 + WHERE level1_no = 30; + +INSERT INTO transition_table_level2 (level2_no, parent_no) + VALUES (10000, 10000); + +UPDATE transition_table_level2 SET parent_no = 2000 + WHERE level2_no = 40; + + +-- attempt modifications which would not break RI (should all succeed) +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 201 AND 1000; + +DELETE FROM transition_table_level1 + WHERE level1_no BETWEEN 100000000 AND 100000010; + +SELECT count(*) FROM transition_table_level1; + +DELETE FROM transition_table_level2 + WHERE level2_no BETWEEN 211 AND 220; + +SELECT count(*) FROM transition_table_level2; + +CREATE TABLE alter_table_under_transition_tables +( + id int PRIMARY KEY, + name text +); + +CREATE FUNCTION alter_table_under_transition_tables_upd_func() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + RAISE WARNING 'old table = %, new table = %', + (SELECT string_agg(id || '=' || name, ',') FROM d), + (SELECT string_agg(id || '=' || name, ',') FROM i); + RAISE NOTICE 'one = %', (SELECT 1 FROM alter_table_under_transition_tables LIMIT 1); + RETURN NULL; +END; +$$; + +CREATE TRIGGER alter_table_under_transition_tables_upd_trigger + AFTER UPDATE ON alter_table_under_transition_tables + REFERENCING OLD TABLE AS d NEW TABLE AS i + FOR EACH STATEMENT EXECUTE PROCEDURE + alter_table_under_transition_tables_upd_func(); + +INSERT INTO alter_table_under_transition_tables + VALUES (1, '1'), (2, '2'), (3, '3'); +UPDATE alter_table_under_transition_tables + SET name = name || name; + +-- now change 'name' to an integer to see what happens... +ALTER TABLE alter_table_under_transition_tables + ALTER COLUMN name TYPE int USING name::integer; +UPDATE alter_table_under_transition_tables + SET name = (name::text || name::text)::integer; + +-- now drop column 'name' +ALTER TABLE alter_table_under_transition_tables + DROP column name; +UPDATE alter_table_under_transition_tables + SET id = id; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index b6de1b3256..4473ce0518 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1240,3 +1240,26 @@ select * from upsert; drop table upsert; drop function upsert_before_func(); drop function upsert_after_func(); + +-- +-- Verify that triggers are prevented on partitioned tables if they would +-- access row data (ROW and STATEMENT-with-transition-table) +-- + +create table my_table (i int) partition by list (i); +create table my_table_42 partition of my_table for values in (42); +create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; +create trigger my_trigger before update on my_table for each row execute procedure my_trigger_function(); +create trigger my_trigger after update on my_table referencing old table as old_table + for each statement execute procedure my_trigger_function(); + +-- +-- Verify that triggers are allowed on partitions +-- +create trigger my_trigger before update on my_table_42 for each row execute procedure my_trigger_function(); +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 table my_table_42; +drop table my_table; -- 2.40.0