diff options
Diffstat (limited to 'src/test/regress/expected/triggers.out')
| -rw-r--r-- | src/test/regress/expected/triggers.out | 3666 |
1 files changed, 3666 insertions, 0 deletions
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out new file mode 100644 index 0000000..cc15f5c --- /dev/null +++ b/src/test/regress/expected/triggers.out @@ -0,0 +1,3666 @@ +-- +-- TRIGGERS +-- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set autoinclib :libdir '/autoinc' :dlsuffix +\set refintlib :libdir '/refint' :dlsuffix +\set regresslib :libdir '/regress' :dlsuffix +CREATE FUNCTION autoinc () + RETURNS trigger + AS :'autoinclib' + LANGUAGE C; +CREATE FUNCTION check_primary_key () + RETURNS trigger + AS :'refintlib' + LANGUAGE C; +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS :'refintlib' + LANGUAGE C; +CREATE FUNCTION trigger_return_old () + RETURNS trigger + AS :'regresslib' + LANGUAGE C; +CREATE FUNCTION set_ttdummy (int4) + RETURNS int4 + AS :'regresslib' + LANGUAGE C STRICT; +create table pkeys (pkey1 int4 not null, pkey2 text not null); +create table fkeys (fkey1 int4, fkey2 text, fkey3 int); +create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); +create index fkeys_i on fkeys (fkey1, fkey2); +create index fkeys2_i on fkeys2 (fkey21, fkey22); +create index fkeys2p_i on fkeys2 (pkey23); +insert into pkeys values (10, '1'); +insert into pkeys values (20, '2'); +insert into pkeys values (30, '3'); +insert into pkeys values (40, '4'); +insert into pkeys values (50, '5'); +insert into pkeys values (60, '6'); +create unique index pkeys_i on pkeys (pkey1, pkey2); +-- +-- For fkeys: +-- (fkey1, fkey2) --> pkeys (pkey1, pkey2) +-- (fkey3) --> fkeys2 (pkey23) +-- +create trigger check_fkeys_pkey_exist + before insert or update on fkeys + for each row + execute function + check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); +create trigger check_fkeys_pkey2_exist + before insert or update on fkeys + for each row + execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); +-- +-- For fkeys2: +-- (fkey21, fkey22) --> pkeys (pkey1, pkey2) +-- +create trigger check_fkeys2_pkey_exist + before insert or update on fkeys2 + for each row + execute procedure + check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); +-- Test comments +COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong'; +ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist +COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right'; +COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; +-- +-- For pkeys: +-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: +-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) +-- +create trigger check_pkeys_fkey_cascade + before delete or update on pkeys + for each row + execute procedure + check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', + 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); +-- +-- For fkeys2: +-- ON DELETE/UPDATE (pkey23) RESTRICT: +-- fkeys (fkey3) +-- +create trigger check_fkeys2_fkey_restrict + before delete or update on fkeys2 + for each row + execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); +insert into fkeys2 values (10, '1', 1); +insert into fkeys2 values (30, '3', 2); +insert into fkeys2 values (40, '4', 5); +insert into fkeys2 values (50, '5', 3); +-- no key in pkeys +insert into fkeys2 values (70, '5', 3); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys". +insert into fkeys values (10, '1', 2); +insert into fkeys values (30, '3', 3); +insert into fkeys values (40, '4', 2); +insert into fkeys values (50, '5', 2); +-- no key in pkeys +insert into fkeys values (70, '5', 1); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys". +-- no key in fkeys2 +insert into fkeys values (60, '6', 4); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2". +delete from pkeys where pkey1 = 30 and pkey2 = '3'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" +CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " +delete from pkeys where pkey1 = 40 and pkey2 = '4'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" +CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, + action_order, action_condition, action_orientation, action_timing, + action_reference_old_table, action_reference_new_table + FROM information_schema.triggers + WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2') + ORDER BY trigger_name COLLATE "C", 2; + trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table +----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- + check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | BEFORE | | + check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | BEFORE | | + check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | BEFORE | | + check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | BEFORE | | + check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | BEFORE | | + check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | BEFORE | | + check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | BEFORE | | + check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | BEFORE | | + check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | BEFORE | | + check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | BEFORE | | +(10 rows) + +DROP TABLE pkeys; +DROP TABLE fkeys; +DROP TABLE fkeys2; +-- Check behavior when trigger returns unmodified trigtuple +create table trigtest (f1 int, f2 text); +create trigger trigger_return_old + before insert or delete or update on trigtest + for each row execute procedure trigger_return_old(); +insert into trigtest values(1, 'foo'); +select * from trigtest; + f1 | f2 +----+----- + 1 | foo +(1 row) + +update trigtest set f2 = f2 || 'bar'; +select * from trigtest; + f1 | f2 +----+----- + 1 | foo +(1 row) + +delete from trigtest; +select * from trigtest; + f1 | f2 +----+---- +(0 rows) + +-- Also check what happens when such a trigger runs before or after others +create function f1_times_10() returns trigger as +$$ begin new.f1 := new.f1 * 10; return new; end $$ language plpgsql; +create trigger trigger_alpha + before insert or update on trigtest + for each row execute procedure f1_times_10(); +insert into trigtest values(1, 'foo'); +select * from trigtest; + f1 | f2 +----+----- + 10 | foo +(1 row) + +update trigtest set f2 = f2 || 'bar'; +select * from trigtest; + f1 | f2 +----+----- + 10 | foo +(1 row) + +delete from trigtest; +select * from trigtest; + f1 | f2 +----+---- +(0 rows) + +create trigger trigger_zed + before insert or update on trigtest + for each row execute procedure f1_times_10(); +insert into trigtest values(1, 'foo'); +select * from trigtest; + f1 | f2 +-----+----- + 100 | foo +(1 row) + +update trigtest set f2 = f2 || 'bar'; +select * from trigtest; + f1 | f2 +------+----- + 1000 | foo +(1 row) + +delete from trigtest; +select * from trigtest; + f1 | f2 +----+---- +(0 rows) + +drop trigger trigger_alpha on trigtest; +insert into trigtest values(1, 'foo'); +select * from trigtest; + f1 | f2 +----+----- + 10 | foo +(1 row) + +update trigtest set f2 = f2 || 'bar'; +select * from trigtest; + f1 | f2 +-----+----- + 100 | foo +(1 row) + +delete from trigtest; +select * from trigtest; + f1 | f2 +----+---- +(0 rows) + +drop table trigtest; +-- Check behavior with an implicit column default, too (bug #16644) +create table trigtest ( + a integer, + b bool default true not null, + c text default 'xyzzy' not null); +create trigger trigger_return_old + before insert or delete or update on trigtest + for each row execute procedure trigger_return_old(); +insert into trigtest values(1); +select * from trigtest; + a | b | c +---+---+------- + 1 | t | xyzzy +(1 row) + +alter table trigtest add column d integer default 42 not null; +select * from trigtest; + a | b | c | d +---+---+-------+---- + 1 | t | xyzzy | 42 +(1 row) + +update trigtest set a = 2 where a = 1 returning *; + a | b | c | d +---+---+-------+---- + 1 | t | xyzzy | 42 +(1 row) + +select * from trigtest; + a | b | c | d +---+---+-------+---- + 1 | t | xyzzy | 42 +(1 row) + +alter table trigtest drop column b; +select * from trigtest; + a | c | d +---+-------+---- + 1 | xyzzy | 42 +(1 row) + +update trigtest set a = 2 where a = 1 returning *; + a | c | d +---+-------+---- + 1 | xyzzy | 42 +(1 row) + +select * from trigtest; + a | c | d +---+-------+---- + 1 | xyzzy | 42 +(1 row) + +drop table trigtest; +create sequence ttdummy_seq increment 10 start 0 minvalue 0; +create table tttest ( + price_id int4, + price_val int4, + price_on int4, + price_off int4 default 999999 +); +create trigger ttdummy + before delete or update on tttest + for each row + execute procedure + ttdummy (price_on, price_off); +create trigger ttserial + before insert or update on tttest + for each row + execute procedure + autoinc (price_on, ttdummy_seq); +insert into tttest values (1, 1, null); +insert into tttest values (2, 2, null); +insert into tttest values (3, 3, 0); +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 2 | 2 | 20 | 999999 + 3 | 3 | 30 | 999999 +(3 rows) + +delete from tttest where price_id = 2; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 3 | 3 | 30 | 999999 + 2 | 2 | 20 | 40 +(3 rows) + +-- what do we see ? +-- get current prices +select * from tttest where price_off = 999999; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 3 | 3 | 30 | 999999 +(2 rows) + +-- change price for price_id == 3 +update tttest set price_val = 30 where price_id = 3; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 2 | 2 | 20 | 40 + 3 | 30 | 50 | 999999 + 3 | 3 | 30 | 50 +(4 rows) + +-- now we want to change pric_id in ALL tuples +-- this gets us not what we need +update tttest set price_id = 5 where price_id = 3; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 2 | 2 | 20 | 40 + 3 | 3 | 30 | 50 + 5 | 30 | 60 | 999999 + 3 | 30 | 50 | 60 +(5 rows) + +-- restore data as before last update: +select set_ttdummy(0); + set_ttdummy +------------- + 1 +(1 row) + +delete from tttest where price_id = 5; +update tttest set price_off = 999999 where price_val = 30; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 2 | 2 | 20 | 40 + 3 | 3 | 30 | 50 + 3 | 30 | 50 | 999999 +(4 rows) + +-- and try change price_id now! +update tttest set price_id = 5 where price_id = 3; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 1 | 1 | 10 | 999999 + 2 | 2 | 20 | 40 + 5 | 3 | 30 | 50 + 5 | 30 | 50 | 999999 +(4 rows) + +-- isn't it what we need ? +select set_ttdummy(1); + set_ttdummy +------------- + 0 +(1 row) + +-- we want to correct some "date" +update tttest set price_on = -1 where price_id = 1; +ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy) +-- but this doesn't work +-- try in this way +select set_ttdummy(0); + set_ttdummy +------------- + 1 +(1 row) + +update tttest set price_on = -1 where price_id = 1; +select * from tttest; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 2 | 2 | 20 | 40 + 5 | 3 | 30 | 50 + 5 | 30 | 50 | 999999 + 1 | 1 | -1 | 999999 +(4 rows) + +-- isn't it what we need ? +-- get price for price_id == 5 as it was @ "date" 35 +select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; + price_id | price_val | price_on | price_off +----------+-----------+----------+----------- + 5 | 3 | 30 | 50 +(1 row) + +drop table tttest; +drop sequence ttdummy_seq; +-- +-- tests for per-statement triggers +-- +CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); +CREATE TABLE main_table (a int unique, b int); +COPY main_table (a,b) FROM stdin; +CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' +BEGIN + RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; + RETURN NULL; +END;'; +CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt'); +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); +-- +-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, +-- CREATE TRIGGER should default to 'FOR EACH STATEMENT' +-- +CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table +EXECUTE PROCEDURE trigger_func('after_upd_stmt'); +-- Both insert and update statement level triggers (before and after) should +-- fire. Doesn't fire UPDATE before trigger, but only because one isn't +-- defined. +INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) + DO UPDATE SET b = EXCLUDED.b; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); +INSERT INTO main_table DEFAULT VALUES; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +UPDATE main_table SET a = a + 1 WHERE b < 30; +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +-- UPDATE that effects zero rows should still call per-statement trigger +UPDATE main_table SET a = a + 2 WHERE b > 100; +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +-- constraint now unneeded +ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; +-- COPY should fire per-row and per-statement INSERT triggers +COPY main_table (a, b) FROM stdin; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +SELECT * FROM main_table ORDER BY a, b; + a | b +----+---- + 6 | 10 + 21 | 20 + 30 | 40 + 31 | 10 + 50 | 35 + 50 | 60 + 81 | 15 + | +(8 rows) + +-- +-- test triggers with WHEN clause +-- +CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); +CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); +CREATE TRIGGER insert_a AFTER INSERT ON main_table +FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); +CREATE TRIGGER delete_a AFTER DELETE ON main_table +FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); +CREATE TRIGGER insert_when BEFORE INSERT ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); +CREATE TRIGGER delete_when AFTER DELETE ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); +SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, + action_order, action_condition, action_orientation, action_timing, + action_reference_old_table, action_reference_new_table + FROM information_schema.triggers + WHERE event_object_table IN ('main_table') + ORDER BY trigger_name COLLATE "C", 2; + trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table +----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+---------------------------- + after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | | + after_upd_row_trig | UPDATE | public | main_table | 1 | | ROW | AFTER | | + after_upd_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | AFTER | | + before_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | BEFORE | | + delete_a | DELETE | public | main_table | 1 | (old.a = 123) | ROW | AFTER | | + delete_when | DELETE | public | main_table | 1 | true | STATEMENT | AFTER | | + insert_a | INSERT | public | main_table | 1 | (new.a = 123) | ROW | AFTER | | + insert_when | INSERT | public | main_table | 2 | true | STATEMENT | BEFORE | | + modified_a | UPDATE | public | main_table | 1 | (old.a <> new.a) | ROW | BEFORE | | + modified_any | UPDATE | public | main_table | 2 | (old.* IS DISTINCT FROM new.*) | ROW | BEFORE | | +(10 rows) + +INSERT INTO main_table (a) VALUES (123), (456); +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +COPY main_table FROM stdin; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +DELETE FROM main_table WHERE a IN (123, 456); +NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW +NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW +NOTICE: trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT +UPDATE main_table SET a = 50, b = 60; +NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +SELECT * FROM main_table ORDER BY a, b; + a | b +----+---- + 6 | 10 + 21 | 20 + 30 | 40 + 31 | 10 + 50 | 35 + 50 | 60 + 81 | 15 + | +(8 rows) + +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; + pg_get_triggerdef +------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a') +(1 row) + +SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; + pg_get_triggerdef +---------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a') +(1 row) + +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; + pg_get_triggerdef +------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any') +(1 row) + +-- Test RENAME TRIGGER +ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a; +SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; + count +------- + 0 +(1 row) + +SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_modified_a'; + count +------- + 1 +(1 row) + +DROP TRIGGER modified_modified_a ON main_table; +DROP TRIGGER modified_any ON main_table; +DROP TRIGGER insert_a ON main_table; +DROP TRIGGER delete_a ON main_table; +DROP TRIGGER insert_when ON main_table; +DROP TRIGGER delete_when ON main_table; +-- Test WHEN condition accessing system columns. +create table table_with_oids(a int); +insert into table_with_oids values (1); +create trigger oid_unchanged_trig after update on table_with_oids + for each row + when (new.tableoid = old.tableoid AND new.tableoid <> 0) + execute procedure trigger_func('after_upd_oid_unchanged'); +update table_with_oids set a = a + 1; +NOTICE: trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW +drop table table_with_oids; +-- Test column-level triggers +DROP TRIGGER after_upd_row_trig ON main_table; +CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row'); +CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row'); +CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row'); +CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt'); +CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt'); +SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig'; + pg_get_triggerdef +------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row') +(1 row) + +UPDATE main_table SET a = 50; +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +UPDATE main_table SET b = 10; +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +-- +-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN +-- +CREATE TABLE some_t (some_col boolean NOT NULL); +CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$ +BEGIN + RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %', + TG_ARGV[0], TG_OP, OLD, NEW; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW + EXECUTE PROCEDURE dummy_update_func('before'); +CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW + WHEN (NOT OLD.some_col AND NEW.some_col) + EXECUTE PROCEDURE dummy_update_func('aftera'); +CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW + WHEN (NOT NEW.some_col) + EXECUTE PROCEDURE dummy_update_func('afterb'); +INSERT INTO some_t VALUES (TRUE); +UPDATE some_t SET some_col = TRUE; +NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (t) +UPDATE some_t SET some_col = FALSE; +NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (f) +NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f) +UPDATE some_t SET some_col = TRUE; +NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t) +NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t) +DROP TABLE some_t; +-- bogus cases +CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col'); +ERROR: duplicate trigger events specified at or near "ON" +LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta... + ^ +CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a'); +ERROR: column "a" specified more than once +CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); +ERROR: syntax error at or near "OF" +LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table + ^ +CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_ins_old'); +ERROR: INSERT trigger's WHEN condition cannot reference OLD values +LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) + ^ +CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_del_new'); +ERROR: DELETE trigger's WHEN condition cannot reference NEW values +LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) + ^ +CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (NEW.tableoid <> 0) +EXECUTE PROCEDURE trigger_func('error_when_sys_column'); +ERROR: BEFORE trigger's WHEN condition cannot reference NEW system columns +LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0) + ^ +CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table +FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) +EXECUTE PROCEDURE trigger_func('error_stmt_when'); +ERROR: statement trigger's WHEN condition cannot reference column values +LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) + ^ +-- check dependency restrictions +ALTER TABLE main_table DROP COLUMN b; +ERROR: cannot drop column b of table main_table because other objects depend on it +DETAIL: trigger after_upd_b_row_trig on table main_table depends on column b of table main_table +trigger after_upd_a_b_row_trig on table main_table depends on column b of table main_table +trigger after_upd_b_stmt_trig on table main_table depends on column b of table main_table +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- this should succeed, but we'll roll it back to keep the triggers around +begin; +DROP TRIGGER after_upd_a_b_row_trig ON main_table; +DROP TRIGGER after_upd_b_row_trig ON main_table; +DROP TRIGGER after_upd_b_stmt_trig ON main_table; +ALTER TABLE main_table DROP COLUMN b; +rollback; +-- Test enable/disable triggers +create table trigtest (i serial primary key); +-- test that disabling RI triggers works +create table trigtest2 (i int references trigtest(i) on delete cascade); +create function trigtest() returns trigger as $$ +begin + raise notice '% % % %', TG_TABLE_NAME, TG_OP, TG_WHEN, TG_LEVEL; + return new; +end;$$ language plpgsql; +create trigger trigtest_b_row_tg before insert or update or delete on trigtest +for each row execute procedure trigtest(); +create trigger trigtest_a_row_tg after insert or update or delete on trigtest +for each row execute procedure trigtest(); +create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest +for each statement execute procedure trigtest(); +create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest +for each statement execute procedure trigtest(); +insert into trigtest default values; +NOTICE: trigtest INSERT BEFORE STATEMENT +NOTICE: trigtest INSERT BEFORE ROW +NOTICE: trigtest INSERT AFTER ROW +NOTICE: trigtest INSERT AFTER STATEMENT +alter table trigtest disable trigger trigtest_b_row_tg; +insert into trigtest default values; +NOTICE: trigtest INSERT BEFORE STATEMENT +NOTICE: trigtest INSERT AFTER ROW +NOTICE: trigtest INSERT AFTER STATEMENT +alter table trigtest disable trigger user; +insert into trigtest default values; +alter table trigtest enable trigger trigtest_a_stmt_tg; +insert into trigtest default values; +NOTICE: trigtest INSERT AFTER STATEMENT +set session_replication_role = replica; +insert into trigtest default values; -- does not trigger +alter table trigtest enable always trigger trigtest_a_stmt_tg; +insert into trigtest default values; -- now it does +NOTICE: trigtest INSERT AFTER STATEMENT +reset session_replication_role; +insert into trigtest2 values(1); +insert into trigtest2 values(2); +delete from trigtest where i=2; +NOTICE: trigtest DELETE AFTER STATEMENT +select * from trigtest2; + i +--- + 1 +(1 row) + +alter table trigtest disable trigger all; +delete from trigtest where i=1; +select * from trigtest2; + i +--- + 1 +(1 row) + +-- ensure we still insert, even when all triggers are disabled +insert into trigtest default values; +select * from trigtest; + i +--- + 3 + 4 + 5 + 6 + 7 +(5 rows) + +drop table trigtest2; +drop table trigtest; +-- dump trigger data +CREATE TABLE trigger_test ( + i int, + v varchar +); +CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger +LANGUAGE plpgsql AS $$ + +declare + + argstr text; + relid text; + +begin + + relid := TG_relid::regclass; + + -- plpgsql can't discover its trigger data in a hash like perl and python + -- can, or by a sort of reflection like tcl can, + -- so we have to hard code the names. + raise NOTICE 'TG_NAME: %', TG_name; + raise NOTICE 'TG_WHEN: %', TG_when; + raise NOTICE 'TG_LEVEL: %', TG_level; + raise NOTICE 'TG_OP: %', TG_op; + raise NOTICE 'TG_RELID::regclass: %', relid; + raise NOTICE 'TG_RELNAME: %', TG_relname; + raise NOTICE 'TG_TABLE_NAME: %', TG_table_name; + raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema; + raise NOTICE 'TG_NARGS: %', TG_nargs; + + argstr := '['; + for i in 0 .. TG_nargs - 1 loop + if i > 0 then + argstr := argstr || ', '; + end if; + argstr := argstr || TG_argv[i]; + end loop; + argstr := argstr || ']'; + raise NOTICE 'TG_ARGV: %', argstr; + + if TG_OP != 'INSERT' then + raise NOTICE 'OLD: %', OLD; + end if; + + if TG_OP != 'DELETE' then + raise NOTICE 'NEW: %', NEW; + end if; + + if TG_OP = 'DELETE' then + return OLD; + else + return NEW; + end if; + +end; +$$; +CREATE TRIGGER show_trigger_data_trig +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +insert into trigger_test values(1,'insert'); +NOTICE: TG_NAME: show_trigger_data_trig +NOTICE: TG_WHEN: BEFORE +NOTICE: TG_LEVEL: ROW +NOTICE: TG_OP: INSERT +NOTICE: TG_RELID::regclass: trigger_test +NOTICE: TG_RELNAME: trigger_test +NOTICE: TG_TABLE_NAME: trigger_test +NOTICE: TG_TABLE_SCHEMA: public +NOTICE: TG_NARGS: 2 +NOTICE: TG_ARGV: [23, skidoo] +NOTICE: NEW: (1,insert) +update trigger_test set v = 'update' where i = 1; +NOTICE: TG_NAME: show_trigger_data_trig +NOTICE: TG_WHEN: BEFORE +NOTICE: TG_LEVEL: ROW +NOTICE: TG_OP: UPDATE +NOTICE: TG_RELID::regclass: trigger_test +NOTICE: TG_RELNAME: trigger_test +NOTICE: TG_TABLE_NAME: trigger_test +NOTICE: TG_TABLE_SCHEMA: public +NOTICE: TG_NARGS: 2 +NOTICE: TG_ARGV: [23, skidoo] +NOTICE: OLD: (1,insert) +NOTICE: NEW: (1,update) +delete from trigger_test; +NOTICE: TG_NAME: show_trigger_data_trig +NOTICE: TG_WHEN: BEFORE +NOTICE: TG_LEVEL: ROW +NOTICE: TG_OP: DELETE +NOTICE: TG_RELID::regclass: trigger_test +NOTICE: TG_RELNAME: trigger_test +NOTICE: TG_TABLE_NAME: trigger_test +NOTICE: TG_TABLE_SCHEMA: public +NOTICE: TG_NARGS: 2 +NOTICE: TG_ARGV: [23, skidoo] +NOTICE: OLD: (1,update) +DROP TRIGGER show_trigger_data_trig on trigger_test; +DROP FUNCTION trigger_data(); +DROP TABLE trigger_test; +-- +-- Test use of row comparisons on OLD/NEW +-- +CREATE TABLE trigger_test (f1 int, f2 text, f3 text); +-- this is the obvious (and wrong...) way to compare rows +CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ +begin + if row(old.*) = row(new.*) then + raise notice 'row % not changed', new.f1; + else + raise notice 'row % changed', new.f1; + end if; + return new; +end$$; +CREATE TRIGGER t +BEFORE UPDATE ON trigger_test +FOR EACH ROW EXECUTE PROCEDURE mytrigger(); +INSERT INTO trigger_test VALUES(1, 'foo', 'bar'); +INSERT INTO trigger_test VALUES(2, 'baz', 'quux'); +UPDATE trigger_test SET f3 = 'bar'; +NOTICE: row 1 not changed +NOTICE: row 2 changed +UPDATE trigger_test SET f3 = NULL; +NOTICE: row 1 changed +NOTICE: row 2 changed +-- this demonstrates that the above isn't really working as desired: +UPDATE trigger_test SET f3 = NULL; +NOTICE: row 1 changed +NOTICE: row 2 changed +-- the right way when considering nulls is +CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ +begin + if row(old.*) is distinct from row(new.*) then + raise notice 'row % changed', new.f1; + else + raise notice 'row % not changed', new.f1; + end if; + return new; +end$$; +UPDATE trigger_test SET f3 = 'bar'; +NOTICE: row 1 changed +NOTICE: row 2 changed +UPDATE trigger_test SET f3 = NULL; +NOTICE: row 1 changed +NOTICE: row 2 changed +UPDATE trigger_test SET f3 = NULL; +NOTICE: row 1 not changed +NOTICE: row 2 not changed +DROP TABLE trigger_test; +DROP FUNCTION mytrigger(); +-- Test snapshot management in serializable transactions involving triggers +-- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com +CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS +$$ +declare + rec record; +begin + new.description = 'updated in trigger'; + return new; +end; +$$; +CREATE TABLE serializable_update_tab ( + id int, + filler text, + description text +); +CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab + FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig(); +INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new' + FROM generate_series(1, 50) a; +BEGIN; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1; +COMMIT; +SELECT description FROM serializable_update_tab WHERE id = 1; + description +-------------------- + updated in trigger +(1 row) + +DROP TABLE serializable_update_tab; +-- minimal update trigger +CREATE TABLE min_updates_test ( + f1 text, + f2 int, + f3 int); +INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); +CREATE TRIGGER z_min_update +BEFORE UPDATE ON min_updates_test +FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); +\set QUIET false +UPDATE min_updates_test SET f1 = f1; +UPDATE 0 +UPDATE min_updates_test SET f2 = f2 + 1; +UPDATE 2 +UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; +UPDATE 1 +\set QUIET true +SELECT * FROM min_updates_test; + f1 | f2 | f3 +----+----+---- + a | 2 | 2 + b | 3 | 2 +(2 rows) + +DROP TABLE min_updates_test; +-- +-- Test triggers on views +-- +CREATE VIEW main_view AS SELECT a, b FROM main_table; +-- VIEW trigger function +CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger +LANGUAGE plpgsql AS $$ +declare + argstr text := ''; +begin + for i in 0 .. TG_nargs - 1 loop + if i > 0 then + argstr := argstr || ', '; + end if; + argstr := argstr || TG_argv[i]; + end loop; + + raise notice '% % % % (%)', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; + + if TG_LEVEL = 'ROW' then + if TG_OP = 'INSERT' then + raise NOTICE 'NEW: %', NEW; + INSERT INTO main_table VALUES (NEW.a, NEW.b); + RETURN NEW; + end if; + + if TG_OP = 'UPDATE' then + raise NOTICE 'OLD: %, NEW: %', OLD, NEW; + UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; + end if; + + if TG_OP = 'DELETE' then + raise NOTICE 'OLD: %', OLD; + DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; + end if; + end if; + + RETURN NULL; +end; +$$; +-- Before row triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +-- After row triggers aren't allowed on views +CREATE TRIGGER invalid_trig AFTER INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig AFTER DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +-- Truncate triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have TRUNCATE triggers. +CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have TRUNCATE triggers. +-- INSTEAD OF triggers aren't allowed on tables +CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +-- Don't support WHEN clauses with INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers cannot have WHEN conditions +-- Don't support column-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers cannot have column lists +-- Don't support statement-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers must be FOR EACH ROW +-- Valid INSTEAD OF triggers +CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); +CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); +-- Valid BEFORE statement VIEW triggers +CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); +CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); +CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); +-- Valid AFTER statement VIEW triggers +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); +CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); +CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); +\set QUIET false +-- Insert into view using trigger +INSERT INTO main_view VALUES (20, 30); +NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) +NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) +NOTICE: NEW: (20,30) +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) +INSERT 0 1 +INSERT INTO main_view VALUES (21, 31) RETURNING a, b; +NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) +NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) +NOTICE: NEW: (21,31) +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) + a | b +----+---- + 21 | 31 +(1 row) + +INSERT 0 1 +-- Table trigger will prevent updates +UPDATE main_view SET b = 31 WHERE a = 20; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (20,30), NEW: (20,31) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 0 +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (21,31), NEW: (21,32) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) + a | b +---+--- +(0 rows) + +UPDATE 0 +-- Remove table trigger to allow updates +DROP TRIGGER before_upd_a_row_trig ON main_table; +DROP TRIGGER +UPDATE main_view SET b = 31 WHERE a = 20; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (20,30), NEW: (20,31) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 1 +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (21,31), NEW: (21,32) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) + a | b +----+---- + 21 | 32 +(1 row) + +UPDATE 1 +-- Before and after stmt triggers should fire even when no rows are affected +UPDATE main_view SET b = 0 WHERE false; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 0 +-- Delete from view using trigger +DELETE FROM main_view WHERE a IN (20,21); +NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (21,10) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (20,31) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (21,32) +NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) +DELETE 3 +DELETE FROM main_view WHERE a = 31 RETURNING a, b; +NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (31,10) +NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) + a | b +----+---- + 31 | 10 +(1 row) + +DELETE 1 +\set QUIET true +-- Describe view should list triggers +\d main_view + View "public.main_view" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Triggers: + after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt') + after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt') + after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt') + before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt') + before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt') + before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt') + instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_del') + instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_ins') + instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd') + +-- Test dropping view triggers +DROP TRIGGER instead_of_insert_trig ON main_view; +DROP TRIGGER instead_of_delete_trig ON main_view; +\d+ main_view + View "public.main_view" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+---------+------------- + a | integer | | | | plain | + b | integer | | | | plain | +View definition: + SELECT main_table.a, + main_table.b + FROM main_table; +Triggers: + after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt') + after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt') + after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt') + before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt') + before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt') + before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt') + instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd') + +DROP VIEW main_view; +-- +-- Test triggers on a join view +-- +CREATE TABLE country_table ( + country_id serial primary key, + country_name text unique not null, + continent text not null +); +INSERT INTO country_table (country_name, continent) + VALUES ('Japan', 'Asia'), + ('UK', 'Europe'), + ('USA', 'North America') + RETURNING *; + country_id | country_name | continent +------------+--------------+--------------- + 1 | Japan | Asia + 2 | UK | Europe + 3 | USA | North America +(3 rows) + +CREATE TABLE city_table ( + city_id serial primary key, + city_name text not null, + population bigint, + country_id int references country_table +); +CREATE VIEW city_view AS + SELECT city_id, city_name, population, country_name, continent + FROM city_table ci + LEFT JOIN country_table co ON co.country_id = ci.country_id; +CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS NOT NULL then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + else + NEW.continent := NULL; + end if; + + if NEW.city_id IS NOT NULL then + INSERT INTO city_table + VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); + else + INSERT INTO city_table(city_name, population, country_id) + VALUES(NEW.city_name, NEW.population, ctry_id) + RETURNING city_id INTO NEW.city_id; + end if; + + RETURN NEW; +end; +$$; +CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_insert(); +CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ +begin + DELETE FROM city_table WHERE city_id = OLD.city_id; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; +end; +$$; +CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_delete(); +CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS DISTINCT FROM OLD.country_name then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population, + country_id = ctry_id + WHERE city_id = OLD.city_id; + else + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population + WHERE city_id = OLD.city_id; + NEW.continent := OLD.continent; + end if; + + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; +end; +$$; +CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_update(); +\set QUIET false +-- INSERT .. RETURNING +INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 2 | London | 7556900 | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; + city_id | city_name | population | country_name | continent +---------+---------------+------------+--------------+--------------- + 3 | Washington DC | | USA | North America +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 123456 | New York | | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +INSERT 0 1 +-- UPDATE .. RETURNING +UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error +ERROR: No such country: "Japon" +CONTEXT: PL/pgSQL function city_update() line 9 at RAISE +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match +UPDATE 0 +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | | Japan | Asia +(1 row) + +UPDATE 1 +UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | 13010279 | Japan | Asia +(1 row) + +UPDATE 1 +UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 123456 | New York | | UK | Europe +(1 row) + +UPDATE 1 +UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+--------------- + 123456 | New York | 8391881 | USA | North America +(1 row) + +UPDATE 1 +UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +UPDATE 1 +UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 + WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; + city_id | city_name | population | country_name | continent | city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+----------- + 2 | London | 7556900 | UK | Europe | 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +UPDATE 1 +-- DELETE .. RETURNING +DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +DELETE 1 +\set QUIET true +-- read-only view with WHERE clause +CREATE VIEW european_city_view AS + SELECT * FROM city_view WHERE continent = 'Europe'; +SELECT count(*) FROM european_city_view; + count +------- + 1 +(1 row) + +CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql +AS 'begin RETURN NULL; end'; +CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE +ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); +\set QUIET false +INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); +INSERT 0 0 +UPDATE european_city_view SET population = 10000; +UPDATE 0 +DELETE FROM european_city_view; +DELETE 0 +\set QUIET true +-- rules bypassing no-op triggers +CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view +DO INSTEAD INSERT INTO city_view +VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) +RETURNING *; +CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view +DO INSTEAD UPDATE city_view SET + city_name = NEW.city_name, + population = NEW.population, + country_name = NEW.country_name +WHERE city_id = OLD.city_id +RETURNING NEW.*; +CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view +DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; +\set QUIET false +-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are +INSERT INTO european_city_view(city_name, country_name) + VALUES ('Cambridge', 'USA') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+--------------- + 4 | Cambridge | | USA | North America +(1 row) + +INSERT 0 1 +UPDATE european_city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge'; +UPDATE 0 +DELETE FROM european_city_view WHERE city_name = 'Cambridge'; +DELETE 0 +-- UPDATE and DELETE via rule and trigger +UPDATE city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | | UK | Europe +(1 row) + +UPDATE 1 +UPDATE european_city_view SET population = 122800 + WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | 122800 | UK | Europe +(1 row) + +UPDATE 1 +DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | 122800 | UK | Europe +(1 row) + +DELETE 1 +-- join UPDATE test +UPDATE city_view v SET population = 599657 + FROM city_table ci, country_table co + WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' + AND v.city_id = ci.city_id AND v.country_name = co.country_name + RETURNING co.country_id, v.country_name, + v.city_id, v.city_name, v.population; + country_id | country_name | city_id | city_name | population +------------+--------------+---------+---------------+------------ + 3 | USA | 3 | Washington DC | 599657 +(1 row) + +UPDATE 1 +\set QUIET true +SELECT * FROM city_view; + city_id | city_name | population | country_name | continent +---------+---------------+------------+--------------+--------------- + 1 | Tokyo | 13010279 | Japan | Asia + 123456 | New York | 8391881 | USA | North America + 2 | London | 7556900 | UK | Europe + 3 | Washington DC | 599657 | USA | North America +(4 rows) + +DROP TABLE city_table CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view city_view +drop cascades to view european_city_view +DROP TABLE country_table; +-- Test pg_trigger_depth() +create table depth_a (id int not null primary key); +create table depth_b (id int not null primary key); +create table depth_c (id int not null primary key); +create function depth_a_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + insert into depth_b values (new.id); + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_a_tr before insert on depth_a + for each row execute procedure depth_a_tf(); +create function depth_b_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + begin + execute 'insert into depth_c values (' || new.id::text || ')'; + exception + when sqlstate 'U9999' then + raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); + end; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + execute 'insert into depth_c values (' || new.id::text || ')'; + end if; + return new; +end; +$$; +create trigger depth_b_tr before insert on depth_b + for each row execute procedure depth_b_tf(); +create function depth_c_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + raise exception sqlstate 'U9999'; + end if; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_c_tr before insert on depth_c + for each row execute procedure depth_c_tf(); +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +insert into depth_a values (1); +NOTICE: depth_a_tr: depth = 1 +NOTICE: depth_b_tr: depth = 2 +NOTICE: depth_c_tr: depth = 3 +NOTICE: SQLSTATE = U9999: depth = 2 +NOTICE: depth_b_tr: depth = 2 +NOTICE: depth_c_tr: depth = 3 +ERROR: U9999 +CONTEXT: PL/pgSQL function depth_c_tf() line 5 at RAISE +SQL statement "insert into depth_c values (1)" +PL/pgSQL function depth_b_tf() line 12 at EXECUTE +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function depth_a_tf() line 4 at SQL statement +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +insert into depth_a values (2); +NOTICE: depth_a_tr: depth = 1 +NOTICE: depth_b_tr: depth = 2 +NOTICE: depth_c_tr: depth = 3 +NOTICE: depth_c_tr: depth = 3 +NOTICE: depth_b_tr: depth = 2 +NOTICE: depth_a_tr: depth = 1 +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +drop table depth_a, depth_b, depth_c; +drop function depth_a_tf(); +drop function depth_b_tf(); +drop function depth_c_tf(); +-- +-- Test updates to rows during firing of BEFORE ROW triggers. +-- As of 9.2, such cases should be rejected (see bug #6123). +-- +create temp table parent ( + aid int not null primary key, + val1 text, + val2 text, + val3 text, + val4 text, + bcnt int not null default 0); +create temp table child ( + bid int not null primary key, + aid int not null, + val1 text); +create function parent_upd_func() + returns trigger language plpgsql as +$$ +begin + if old.val1 <> new.val1 then + new.val2 = new.val1; + delete from child where child.aid = new.aid and child.val1 = new.val1; + end if; + return new; +end; +$$; +create trigger parent_upd_trig before update on parent + for each row execute procedure parent_upd_func(); +create function parent_del_func() + returns trigger language plpgsql as +$$ +begin + delete from child where aid = old.aid; + return old; +end; +$$; +create trigger parent_del_trig before delete on parent + for each row execute procedure parent_del_func(); +create function child_ins_func() + returns trigger language plpgsql as +$$ +begin + update parent set bcnt = bcnt + 1 where aid = new.aid; + return new; +end; +$$; +create trigger child_ins_trig after insert on child + for each row execute procedure child_ins_func(); +create function child_del_func() + returns trigger language plpgsql as +$$ +begin + update parent set bcnt = bcnt - 1 where aid = old.aid; + return old; +end; +$$; +create trigger child_del_trig after delete on child + for each row execute procedure child_del_func(); +insert into parent values (1, 'a', 'a', 'a', 'a', 0); +insert into child values (10, 1, 'b'); +select * from parent; select * from child; + aid | val1 | val2 | val3 | val4 | bcnt +-----+------+------+------+------+------ + 1 | a | a | a | a | 1 +(1 row) + + bid | aid | val1 +-----+-----+------ + 10 | 1 | b +(1 row) + +update parent set val1 = 'b' where aid = 1; -- should fail +ERROR: tuple to be updated was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +select * from parent; select * from child; + aid | val1 | val2 | val3 | val4 | bcnt +-----+------+------+------+------+------ + 1 | a | a | a | a | 1 +(1 row) + + bid | aid | val1 +-----+-----+------ + 10 | 1 | b +(1 row) + +delete from parent where aid = 1; -- should fail +ERROR: tuple to be deleted was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +select * from parent; select * from child; + aid | val1 | val2 | val3 | val4 | bcnt +-----+------+------+------+------+------ + 1 | a | a | a | a | 1 +(1 row) + + bid | aid | val1 +-----+-----+------ + 10 | 1 | b +(1 row) + +-- replace the trigger function with one that restarts the deletion after +-- having modified a child +create or replace function parent_del_func() + returns trigger language plpgsql as +$$ +begin + delete from child where aid = old.aid; + if found then + delete from parent where aid = old.aid; + return null; -- cancel outer deletion + end if; + return old; +end; +$$; +delete from parent where aid = 1; +select * from parent; select * from child; + aid | val1 | val2 | val3 | val4 | bcnt +-----+------+------+------+------+------ +(0 rows) + + bid | aid | val1 +-----+-----+------ +(0 rows) + +drop table parent, child; +drop function parent_upd_func(); +drop function parent_del_func(); +drop function child_ins_func(); +drop function child_del_func(); +-- similar case, but with a self-referencing FK so that parent and child +-- rows can be affected by a single operation +create temp table self_ref_trigger ( + id int primary key, + parent int references self_ref_trigger, + data text, + nchildren int not null default 0 +); +create function self_ref_trigger_ins_func() + returns trigger language plpgsql as +$$ +begin + if new.parent is not null then + update self_ref_trigger set nchildren = nchildren + 1 + where id = new.parent; + end if; + return new; +end; +$$; +create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger + for each row execute procedure self_ref_trigger_ins_func(); +create function self_ref_trigger_del_func() + returns trigger language plpgsql as +$$ +begin + if old.parent is not null then + update self_ref_trigger set nchildren = nchildren - 1 + where id = old.parent; + end if; + return old; +end; +$$; +create trigger self_ref_trigger_del_trig before delete on self_ref_trigger + for each row execute procedure self_ref_trigger_del_func(); +insert into self_ref_trigger values (1, null, 'root'); +insert into self_ref_trigger values (2, 1, 'root child A'); +insert into self_ref_trigger values (3, 1, 'root child B'); +insert into self_ref_trigger values (4, 2, 'grandchild 1'); +insert into self_ref_trigger values (5, 3, 'grandchild 2'); +update self_ref_trigger set data = 'root!' where id = 1; +select * from self_ref_trigger; + id | parent | data | nchildren +----+--------+--------------+----------- + 2 | 1 | root child A | 1 + 4 | 2 | grandchild 1 | 0 + 3 | 1 | root child B | 1 + 5 | 3 | grandchild 2 | 0 + 1 | | root! | 2 +(5 rows) + +delete from self_ref_trigger; +ERROR: tuple to be updated was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +select * from self_ref_trigger; + id | parent | data | nchildren +----+--------+--------------+----------- + 2 | 1 | root child A | 1 + 4 | 2 | grandchild 1 | 0 + 3 | 1 | root child B | 1 + 5 | 3 | grandchild 2 | 0 + 1 | | root! | 2 +(5 rows) + +drop table self_ref_trigger; +drop function self_ref_trigger_ins_func(); +drop function self_ref_trigger_del_func(); +-- +-- Check that statement triggers work correctly even with all children excluded +-- +create table stmt_trig_on_empty_upd (a int); +create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd); +create function update_stmt_notice() returns trigger as $$ +begin + raise notice 'updating %', TG_TABLE_NAME; + return null; +end; +$$ language plpgsql; +create trigger before_stmt_trigger + before update on stmt_trig_on_empty_upd + execute procedure update_stmt_notice(); +create trigger before_stmt_trigger + before update on stmt_trig_on_empty_upd1 + execute procedure update_stmt_notice(); +-- inherited no-op update +update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa; +NOTICE: updating stmt_trig_on_empty_upd + aa +---- +(0 rows) + +-- simple no-op update +update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa; +NOTICE: updating stmt_trig_on_empty_upd1 + aa +---- +(0 rows) + +drop table stmt_trig_on_empty_upd cascade; +NOTICE: drop cascades to table stmt_trig_on_empty_upd1 +drop function update_stmt_notice(); +-- +-- Check that index creation (or DDL in general) is prohibited in a trigger +-- +create table trigger_ddl_table ( + col1 integer, + col2 integer +); +create function trigger_ddl_func() returns trigger as $$ +begin + alter table trigger_ddl_table add primary key (col1); + return new; +end$$ language plpgsql; +create trigger trigger_ddl_func before insert on trigger_ddl_table for each row + execute procedure trigger_ddl_func(); +insert into trigger_ddl_table values (1, 42); -- fail +ERROR: cannot ALTER TABLE "trigger_ddl_table" because it is being used by active queries in this session +CONTEXT: SQL statement "alter table trigger_ddl_table add primary key (col1)" +PL/pgSQL function trigger_ddl_func() line 3 at SQL statement +create or replace function trigger_ddl_func() returns trigger as $$ +begin + create index on trigger_ddl_table (col2); + return new; +end$$ language plpgsql; +insert into trigger_ddl_table values (1, 42); -- fail +ERROR: cannot CREATE INDEX "trigger_ddl_table" because it is being used by active queries in this session +CONTEXT: SQL statement "create index on trigger_ddl_table (col2)" +PL/pgSQL function trigger_ddl_func() line 3 at SQL statement +drop table trigger_ddl_table; +drop function trigger_ddl_func(); +-- +-- Verify behavior of before and after triggers with INSERT...ON CONFLICT +-- DO UPDATE +-- +create table upsert (key int4 primary key, color text); +create function upsert_before_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'before update (old): %', old.*::text; + raise warning 'before update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'before insert (new): %', new.*::text; + if new.key % 2 = 0 then + new.key := new.key + 1; + new.color := new.color || ' trig modified'; + raise warning 'before insert (new, modified): %', new.*::text; + end if; + end if; + return new; +end; +$$; +create trigger upsert_before_trig before insert or update on upsert + for each row execute procedure upsert_before_func(); +create function upsert_after_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'after update (old): %', old.*::text; + raise warning 'after update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'after insert (new): %', new.*::text; + end if; + return null; +end; +$$; +create trigger upsert_after_trig after insert or update on upsert + for each row execute procedure upsert_after_func(); +insert into upsert values(1, 'black') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (1,black) +WARNING: after insert (new): (1,black) +insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (2,red) +WARNING: before insert (new, modified): (3,"red trig modified") +WARNING: after insert (new): (3,"red trig modified") +insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (3,orange) +WARNING: before update (old): (3,"red trig modified") +WARNING: before update (new): (3,"updated red trig modified") +WARNING: after update (old): (3,"red trig modified") +WARNING: after update (new): (3,"updated red trig modified") +insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (4,green) +WARNING: before insert (new, modified): (5,"green trig modified") +WARNING: after insert (new): (5,"green trig modified") +insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (5,purple) +WARNING: before update (old): (5,"green trig modified") +WARNING: before update (new): (5,"updated green trig modified") +WARNING: after update (old): (5,"green trig modified") +WARNING: after update (new): (5,"updated green trig modified") +insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (6,white) +WARNING: before insert (new, modified): (7,"white trig modified") +WARNING: after insert (new): (7,"white trig modified") +insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (7,pink) +WARNING: before update (old): (7,"white trig modified") +WARNING: before update (new): (7,"updated white trig modified") +WARNING: after update (old): (7,"white trig modified") +WARNING: after update (new): (7,"updated white trig modified") +insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (8,yellow) +WARNING: before insert (new, modified): (9,"yellow trig modified") +WARNING: after insert (new): (9,"yellow trig modified") +select * from upsert; + key | color +-----+----------------------------- + 1 | black + 3 | updated red trig modified + 5 | updated green trig modified + 7 | updated white trig modified + 9 | yellow trig modified +(5 rows) + +drop table upsert; +drop function upsert_before_func(); +drop function upsert_after_func(); +-- +-- 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 cases that are unsupported with partitioned tables +-- +create table parted_trig (a int) partition by list (a); +create function trigger_nothing() returns trigger + language plpgsql as $$ begin end; $$; +create trigger failed instead of update on parted_trig + for each row execute procedure trigger_nothing(); +ERROR: "parted_trig" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +create trigger failed after update on parted_trig + referencing old table as old_table + for each row execute procedure trigger_nothing(); +ERROR: "parted_trig" is a partitioned table +DETAIL: ROW triggers with transition tables are not supported on partitioned tables. +drop table parted_trig; +-- +-- Verify trigger creation for partitioned tables, and drop behavior +-- +create table trigpart (a int, b int) partition by range (a); +create table trigpart1 partition of trigpart for values from (0) to (1000); +create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing(); +create table trigpart2 partition of trigpart for values from (1000) to (2000); +create table trigpart3 (like trigpart); +alter table trigpart attach partition trigpart3 for values from (2000) to (3000); +create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a); +create table trigpart41 partition of trigpart4 for values from (3000) to (3500); +create table trigpart42 (like trigpart); +alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000); +select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger + where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; + tgrelid | tgname | tgfoid +------------+--------+----------------- + trigpart | trg1 | trigger_nothing + trigpart1 | trg1 | trigger_nothing + trigpart2 | trg1 | trigger_nothing + trigpart3 | trg1 | trigger_nothing + trigpart4 | trg1 | trigger_nothing + trigpart41 | trg1 | trigger_nothing + trigpart42 | trg1 | trigger_nothing +(7 rows) + +drop trigger trg1 on trigpart1; -- fail +ERROR: cannot drop trigger trg1 on table trigpart1 because trigger trg1 on table trigpart requires it +HINT: You can drop trigger trg1 on table trigpart instead. +drop trigger trg1 on trigpart2; -- fail +ERROR: cannot drop trigger trg1 on table trigpart2 because trigger trg1 on table trigpart requires it +HINT: You can drop trigger trg1 on table trigpart instead. +drop trigger trg1 on trigpart3; -- fail +ERROR: cannot drop trigger trg1 on table trigpart3 because trigger trg1 on table trigpart requires it +HINT: You can drop trigger trg1 on table trigpart instead. +drop table trigpart2; -- ok, trigger should be gone in that partition +select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger + where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; + tgrelid | tgname | tgfoid +------------+--------+----------------- + trigpart | trg1 | trigger_nothing + trigpart1 | trg1 | trigger_nothing + trigpart3 | trg1 | trigger_nothing + trigpart4 | trg1 | trigger_nothing + trigpart41 | trg1 | trigger_nothing + trigpart42 | trg1 | trigger_nothing +(6 rows) + +drop trigger trg1 on trigpart; -- ok, all gone +select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger + where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; + tgrelid | tgname | tgfoid +---------+--------+-------- +(0 rows) + +-- check detach behavior +create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing(); +\d trigpart3 + Table "public.trigpart3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Partition of: trigpart FOR VALUES FROM (2000) TO (3000) +Triggers: + trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart + +alter table trigpart detach partition trigpart3; +drop trigger trg1 on trigpart3; -- fail due to "does not exist" +ERROR: trigger "trg1" for table "trigpart3" does not exist +alter table trigpart detach partition trigpart4; +drop trigger trg1 on trigpart41; -- fail due to "does not exist" +ERROR: trigger "trg1" for table "trigpart41" does not exist +drop table trigpart4; +alter table trigpart attach partition trigpart3 for values from (2000) to (3000); +alter table trigpart detach partition trigpart3; +alter table trigpart attach partition trigpart3 for values from (2000) to (3000); +drop table trigpart3; +select tgrelid::regclass::text, tgname, tgfoid::regproc, tgenabled, tgisinternal from pg_trigger + where tgname ~ '^trg1' order by 1; + tgrelid | tgname | tgfoid | tgenabled | tgisinternal +-----------+--------+-----------------+-----------+-------------- + trigpart | trg1 | trigger_nothing | O | f + trigpart1 | trg1 | trigger_nothing | O | f +(2 rows) + +create table trigpart3 (like trigpart); +create trigger trg1 after insert on trigpart3 for each row execute procedure trigger_nothing(); +\d trigpart3 + Table "public.trigpart3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Triggers: + trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing() + +alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail +ERROR: trigger "trg1" for relation "trigpart3" already exists +drop table trigpart3; +-- check display of unrelated triggers +create trigger samename after delete on trigpart execute function trigger_nothing(); +create trigger samename after delete on trigpart1 execute function trigger_nothing(); +\d trigpart1 + Table "public.trigpart1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Partition of: trigpart FOR VALUES FROM (0) TO (1000) +Triggers: + samename AFTER DELETE ON trigpart1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_nothing() + trg1 AFTER INSERT ON trigpart1 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart + +drop table trigpart; +drop function trigger_nothing(); +-- +-- Verify that triggers are fired for partitioned tables +-- +create table parted_stmt_trig (a int) partition by list (a); +create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1); +create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); +create table parted2_stmt_trig (a int) partition by list (a); +create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); +create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); +create or replace function trigger_notice() returns trigger as $$ + begin + raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; + if TG_LEVEL = 'ROW' then + return NEW; + end if; + return null; + end; + $$ language plpgsql; +-- insert/update/delete statement-level triggers on the parent +create trigger trig_ins_before before insert on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_ins_after after insert on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after after delete on parted_stmt_trig + for each statement execute procedure trigger_notice(); +-- insert/update/delete row-level triggers on the parent +create trigger trig_ins_after_parent after insert on parted_stmt_trig + for each row execute procedure trigger_notice(); +create trigger trig_upd_after_parent after update on parted_stmt_trig + for each row execute procedure trigger_notice(); +create trigger trig_del_after_parent after delete on parted_stmt_trig + for each row execute procedure trigger_notice(); +-- insert/update/delete row-level triggers on the first partition +create trigger trig_ins_before_child before insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_ins_after_child after insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_before_child before update on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_after_child after update on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_del_before_child before delete on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_del_after_child after delete on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +-- insert/update/delete statement-level triggers on the parent +create trigger trig_ins_before_3 before insert on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_ins_after_3 after insert on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before_3 before update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after_3 after update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before_3 before delete on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after_3 after delete on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +with ins (a) as ( + insert into parted2_stmt_trig values (1), (2) returning a +) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a; +NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT +NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT + tableoid | a +-------------------+--- + parted_stmt_trig1 | 1 + parted_stmt_trig2 | 2 +(2 rows) + +with upd as ( + update parted2_stmt_trig set a = a +) update parted_stmt_trig set a = a; +NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT +NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW +NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT +NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW +NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW +NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW +NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT +NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT +delete from parted_stmt_trig; +NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT +NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW +NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW +NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT +-- insert via copy on the parent +copy parted_stmt_trig(a) from stdin; +NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT +-- insert via copy on the first partition +copy parted_stmt_trig1(a) from stdin; +NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW +-- Disabling a trigger in the parent table should disable children triggers too +alter table parted_stmt_trig disable trigger trig_ins_after_parent; +insert into parted_stmt_trig values (1); +NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT +alter table parted_stmt_trig enable trigger trig_ins_after_parent; +insert into parted_stmt_trig values (1); +NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT +drop table parted_stmt_trig, parted2_stmt_trig; +-- Verify that triggers fire in alphabetical order +create table parted_trig (a int) partition by range (a); +create table parted_trig_1 partition of parted_trig for values from (0) to (1000) + partition by range (a); +create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100); +create table parted_trig_2 partition of parted_trig for values from (1000) to (2000); +create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice(); +create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice(); +create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice(); +create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice(); +create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice(); +insert into parted_trig values (50), (1500); +NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW +NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW +NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW +NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW +NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW +NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW +NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW +drop table parted_trig; +-- Verify propagation of trigger arguments to partitions +create table parted_trig (a int) partition by list (a); +create table parted_trig1 partition of parted_trig for values in (1); +create or replace function trigger_notice() returns trigger as $$ + declare + arg1 text = TG_ARGV[0]; + arg2 integer = TG_ARGV[1]; + begin + raise notice 'trigger % on % % % for % args % %', + TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, arg1, arg2; + return null; + end; + $$ language plpgsql; +create trigger aaa after insert on parted_trig + for each row execute procedure trigger_notice('quirky', 1); +-- Verify propagation of trigger arguments to partitions attached after creating trigger +create table parted_trig2 partition of parted_trig for values in (2); +create table parted_trig3 (like parted_trig); +alter table parted_trig attach partition parted_trig3 for values in (3); +insert into parted_trig values (1), (2), (3); +NOTICE: trigger aaa on parted_trig1 AFTER INSERT for ROW args quirky 1 +NOTICE: trigger aaa on parted_trig2 AFTER INSERT for ROW args quirky 1 +NOTICE: trigger aaa on parted_trig3 AFTER INSERT for ROW args quirky 1 +drop table parted_trig; +-- test irregular partitions (i.e., different column definitions), +-- including that the WHEN clause works +create function bark(text) returns bool language plpgsql immutable + as $$ begin raise notice '% <- woof!', $1; return true; end; $$; +create or replace function trigger_notice_ab() returns trigger as $$ + begin + raise notice 'trigger % on % % % for %: (a,b)=(%,%)', + TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, + NEW.a, NEW.b; + if TG_LEVEL = 'ROW' then + return NEW; + end if; + return null; + end; + $$ language plpgsql; +create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int) + partition by range (b); +alter table parted_irreg_ancestor drop column fd, + drop column fd2, drop column fd3; +create table parted_irreg (fd int, a int, fd2 int, b text) + partition by range (b); +alter table parted_irreg drop column fd, drop column fd2; +alter table parted_irreg_ancestor attach partition parted_irreg + for values from ('aaaa') to ('zzzz'); +create table parted1_irreg (b text, fd int, a int); +alter table parted1_irreg drop column fd; +alter table parted_irreg attach partition parted1_irreg + for values from ('aaaa') to ('bbbb'); +create trigger parted_trig after insert on parted_irreg + for each row execute procedure trigger_notice_ab(); +create trigger parted_trig_odd after insert on parted_irreg for each row + when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab(); +-- we should hear barking for every insert, but parted_trig_odd only emits +-- noise for odd values of a. parted_trig does it for all inserts. +insert into parted_irreg values (1, 'aardvark'), (2, 'aanimals'); +NOTICE: aardvark <- woof! +NOTICE: aanimals <- woof! +NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark) +NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark) +NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aanimals) +insert into parted1_irreg values ('aardwolf', 2); +NOTICE: aardwolf <- woof! +NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aardwolf) +insert into parted_irreg_ancestor values ('aasvogel', 3); +NOTICE: aasvogel <- woof! +NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel) +NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel) +drop table parted_irreg_ancestor; +-- Before triggers and partitions +create table parted (a int, b int, c text) partition by list (a); +create table parted_1 partition of parted for values in (1) + partition by list (b); +create table parted_1_1 partition of parted_1 for values in (1); +create function parted_trigfunc() returns trigger language plpgsql as $$ +begin + new.a = new.a + 1; + return new; +end; +$$; +insert into parted values (1, 1, 'uno uno v1'); -- works +create trigger t before insert or update or delete on parted + for each row execute function parted_trigfunc(); +insert into parted values (1, 1, 'uno uno v2'); -- fail +ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported +DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1". +update parted set c = c || 'v3'; -- fail +ERROR: no partition of relation "parted" found for row +DETAIL: Partition key of the failing row contains (a) = (2). +create or replace function parted_trigfunc() returns trigger language plpgsql as $$ +begin + new.b = new.b + 1; + return new; +end; +$$; +insert into parted values (1, 1, 'uno uno v4'); -- fail +ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported +DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1". +update parted set c = c || 'v5'; -- fail +ERROR: no partition of relation "parted_1" found for row +DETAIL: Partition key of the failing row contains (b) = (2). +create or replace function parted_trigfunc() returns trigger language plpgsql as $$ +begin + new.c = new.c || ' did '|| TG_OP; + return new; +end; +$$; +insert into parted values (1, 1, 'uno uno'); -- works +update parted set c = c || ' v6'; -- works +select tableoid::regclass, * from parted; + tableoid | a | b | c +------------+---+---+---------------------------------- + parted_1_1 | 1 | 1 | uno uno v1 v6 did UPDATE + parted_1_1 | 1 | 1 | uno uno did INSERT v6 did UPDATE +(2 rows) + +-- update itself moves tuple to new partition; trigger still works +truncate table parted; +create table parted_2 partition of parted for values in (2); +insert into parted values (1, 1, 'uno uno v5'); +update parted set a = 2; +select tableoid::regclass, * from parted; + tableoid | a | b | c +----------+---+---+--------------------------------------------- + parted_2 | 2 | 1 | uno uno v5 did INSERT did UPDATE did INSERT +(1 row) + +-- both trigger and update change the partition +create or replace function parted_trigfunc2() returns trigger language plpgsql as $$ +begin + new.a = new.a + 1; + return new; +end; +$$; +create trigger t2 before update on parted + for each row execute function parted_trigfunc2(); +truncate table parted; +insert into parted values (1, 1, 'uno uno v6'); +create table parted_3 partition of parted for values in (3); +update parted set a = a + 1; +select tableoid::regclass, * from parted; + tableoid | a | b | c +----------+---+---+--------------------------------------------- + parted_3 | 3 | 1 | uno uno v6 did INSERT did UPDATE did INSERT +(1 row) + +-- there's no partition for a=0, but this update works anyway because +-- the trigger causes the tuple to be routed to another partition +update parted set a = 0; +select tableoid::regclass, * from parted; + tableoid | a | b | c +------------+---+---+------------------------------------------------------------------- + parted_1_1 | 1 | 1 | uno uno v6 did INSERT did UPDATE did INSERT did UPDATE did INSERT +(1 row) + +drop table parted; +create table parted (a int, b int, c text) partition by list ((a + b)); +create or replace function parted_trigfunc() returns trigger language plpgsql as $$ +begin + new.a = new.a + new.b; + return new; +end; +$$; +create table parted_1 partition of parted for values in (1, 2); +create table parted_2 partition of parted for values in (3, 4); +create trigger t before insert or update on parted + for each row execute function parted_trigfunc(); +insert into parted values (0, 1, 'zero win'); +insert into parted values (1, 1, 'one fail'); +ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported +DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1". +insert into parted values (1, 2, 'two fail'); +ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported +DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_2". +select * from parted; + a | b | c +---+---+---------- + 1 | 1 | zero win +(1 row) + +drop table parted; +drop function parted_trigfunc(); +-- +-- Constraint triggers and partitioned tables +create table parted_constr_ancestor (a int, b text) + partition by range (b); +create table parted_constr (a int, b text) + partition by range (b); +alter table parted_constr_ancestor attach partition parted_constr + for values from ('aaaa') to ('zzzz'); +create table parted1_constr (a int, b text); +alter table parted_constr attach partition parted1_constr + for values from ('aaaa') to ('bbbb'); +create constraint trigger parted_trig after insert on parted_constr_ancestor + deferrable + for each row execute procedure trigger_notice_ab(); +create constraint trigger parted_trig_two after insert on parted_constr + deferrable initially deferred + for each row when (bark(new.b) AND new.a % 2 = 1) + execute procedure trigger_notice_ab(); +-- The immediate constraint is fired immediately; the WHEN clause of the +-- deferred constraint is also called immediately. The deferred constraint +-- is fired at commit time. +begin; +insert into parted_constr values (1, 'aardvark'); +NOTICE: aardvark <- woof! +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) +insert into parted1_constr values (2, 'aardwolf'); +NOTICE: aardwolf <- woof! +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf) +insert into parted_constr_ancestor values (3, 'aasvogel'); +NOTICE: aasvogel <- woof! +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) +commit; +NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) +NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) +-- The WHEN clause is immediate, and both constraint triggers are fired at +-- commit time. +begin; +set constraints parted_trig deferred; +insert into parted_constr values (1, 'aardvark'); +NOTICE: aardvark <- woof! +insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel'); +NOTICE: aardwolf <- woof! +NOTICE: aasvogel <- woof! +commit; +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) +NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf) +NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) +NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) +drop table parted_constr_ancestor; +drop function bark(text); +-- Test that the WHEN clause is set properly to partitions +create table parted_trigger (a int, b text) partition by range (a); +create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); +create table parted_trigger_2 (drp int, a int, b text); +alter table parted_trigger_2 drop column drp; +alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); +create trigger parted_trigger after update on parted_trigger + for each row when (new.a % 2 = 1 and length(old.b) >= 2) execute procedure trigger_notice_ab(); +create table parted_trigger_3 (b text, a int) partition by range (length(b)); +create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3); +create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5); +alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); +insert into parted_trigger values + (0, 'a'), (1, 'bbb'), (2, 'bcd'), (3, 'c'), + (1000, 'c'), (1001, 'ddd'), (1002, 'efg'), (1003, 'f'), + (2000, 'e'), (2001, 'fff'), (2002, 'ghi'), (2003, 'h'); +update parted_trigger set a = a + 2; -- notice for odd 'a' values, long 'b' values +NOTICE: trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(3,bbb) +NOTICE: trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1003,ddd) +NOTICE: trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,fff) +drop table parted_trigger; +-- try a constraint trigger, also +create table parted_referenced (a int); +create table unparted_trigger (a int, b text); -- for comparison purposes +create table parted_trigger (a int, b text) partition by range (a); +create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); +create table parted_trigger_2 (drp int, a int, b text); +alter table parted_trigger_2 drop column drp; +alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); +create constraint trigger parted_trigger after update on parted_trigger + from parted_referenced + for each row execute procedure trigger_notice_ab(); +create constraint trigger parted_trigger after update on unparted_trigger + from parted_referenced + for each row execute procedure trigger_notice_ab(); +create table parted_trigger_3 (b text, a int) partition by range (length(b)); +create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3); +create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5); +alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); +select tgname, conname, t.tgrelid::regclass, t.tgconstrrelid::regclass, + c.conrelid::regclass, c.confrelid::regclass + from pg_trigger t join pg_constraint c on (t.tgconstraint = c.oid) + where tgname = 'parted_trigger' + order by t.tgrelid::regclass::text; + tgname | conname | tgrelid | tgconstrrelid | conrelid | confrelid +----------------+----------------+--------------------+-------------------+--------------------+----------- + parted_trigger | parted_trigger | parted_trigger | parted_referenced | parted_trigger | - + parted_trigger | parted_trigger | parted_trigger_1 | parted_referenced | parted_trigger_1 | - + parted_trigger | parted_trigger | parted_trigger_2 | parted_referenced | parted_trigger_2 | - + parted_trigger | parted_trigger | parted_trigger_3 | parted_referenced | parted_trigger_3 | - + parted_trigger | parted_trigger | parted_trigger_3_1 | parted_referenced | parted_trigger_3_1 | - + parted_trigger | parted_trigger | parted_trigger_3_2 | parted_referenced | parted_trigger_3_2 | - + parted_trigger | parted_trigger | unparted_trigger | parted_referenced | unparted_trigger | - +(7 rows) + +drop table parted_referenced, parted_trigger, unparted_trigger; +-- verify that the "AFTER UPDATE OF columns" event is propagated correctly +create table parted_trigger (a int, b text) partition by range (a); +create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); +create table parted_trigger_2 (drp int, a int, b text); +alter table parted_trigger_2 drop column drp; +alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); +create trigger parted_trigger after update of b on parted_trigger + for each row execute procedure trigger_notice_ab(); +create table parted_trigger_3 (b text, a int) partition by range (length(b)); +create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (4); +create table parted_trigger_3_2 partition of parted_trigger_3 for values from (4) to (8); +alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); +insert into parted_trigger values (0, 'a'), (1000, 'c'), (2000, 'e'), (2001, 'eeee'); +update parted_trigger set a = a + 2; -- no notices here +update parted_trigger set b = b || 'b'; -- all triggers should fire +NOTICE: trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(2,ab) +NOTICE: trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1002,cb) +NOTICE: trigger parted_trigger on parted_trigger_3_1 AFTER UPDATE for ROW: (a,b)=(2002,eb) +NOTICE: trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,eeeeb) +drop table parted_trigger; +drop function trigger_notice_ab(); +-- Make sure we don't end up with unnecessary copies of triggers, when +-- cloning them. +create table trg_clone (a int) partition by range (a); +create table trg_clone1 partition of trg_clone for values from (0) to (1000); +alter table trg_clone add constraint uniq unique (a) deferrable; +create table trg_clone2 partition of trg_clone for values from (1000) to (2000); +create table trg_clone3 partition of trg_clone for values from (2000) to (3000) + partition by range (a); +create table trg_clone_3_3 partition of trg_clone3 for values from (2000) to (2100); +select tgrelid::regclass, count(*) from pg_trigger + where tgrelid::regclass in ('trg_clone', 'trg_clone1', 'trg_clone2', + 'trg_clone3', 'trg_clone_3_3') + group by tgrelid::regclass order by tgrelid::regclass; + tgrelid | count +---------------+------- + trg_clone | 1 + trg_clone1 | 1 + trg_clone2 | 1 + trg_clone3 | 1 + trg_clone_3_3 | 1 +(5 rows) + +drop table trg_clone; +-- Test the interaction between ALTER TABLE .. DISABLE TRIGGER and +-- both kinds of inheritance. Historically, legacy inheritance has +-- not recursed to children, so that behavior is preserved. +create table parent (a int); +create table child1 () inherits (parent); +create function trig_nothing() returns trigger language plpgsql + as $$ begin return null; end $$; +create trigger tg after insert on parent + for each row execute function trig_nothing(); +create trigger tg after insert on child1 + for each row execute function trig_nothing(); +alter table parent disable trigger tg; +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text; + tgrelid | tgname | tgenabled +---------+--------+----------- + child1 | tg | O + parent | tg | D +(2 rows) + +alter table only parent enable always trigger tg; +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text; + tgrelid | tgname | tgenabled +---------+--------+----------- + child1 | tg | O + parent | tg | A +(2 rows) + +drop table parent, child1; +create table parent (a int) partition by list (a); +create table child1 partition of parent for values in (1); +create trigger tg after insert on parent + for each row execute procedure trig_nothing(); +create trigger tg_stmt after insert on parent + for statement execute procedure trig_nothing(); +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgname; + tgrelid | tgname | tgenabled +---------+---------+----------- + child1 | tg | O + parent | tg | O + parent | tg_stmt | O +(3 rows) + +alter table only parent enable always trigger tg; -- no recursion because ONLY +alter table parent enable always trigger tg_stmt; -- no recursion because statement trigger +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgname; + tgrelid | tgname | tgenabled +---------+---------+----------- + child1 | tg | O + parent | tg | A + parent | tg_stmt | A +(3 rows) + +-- The following is a no-op for the parent trigger but not so +-- for the child trigger, so recursion should be applied. +alter table parent enable always trigger tg; +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgname; + tgrelid | tgname | tgenabled +---------+---------+----------- + child1 | tg | A + parent | tg | A + parent | tg_stmt | A +(3 rows) + +-- This variant malfunctioned in some releases. +alter table parent disable trigger user; +select tgrelid::regclass, tgname, tgenabled from pg_trigger + where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgname; + tgrelid | tgname | tgenabled +---------+---------+----------- + child1 | tg | D + parent | tg | D + parent | tg_stmt | D +(3 rows) + +drop table parent, child1; +-- Check processing of foreign key triggers +create table parent (a int primary key, f int references parent) + partition by list (a); +create table child1 partition of parent for values in (1); +select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname, + tgfoid::regproc, tgenabled + from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgfoid; + tgrelid | tgname | tgfoid | tgenabled +---------+-------------------------+------------------------+----------- + child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O + child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O + parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O + parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O + parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O + parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O +(6 rows) + +alter table parent disable trigger all; +select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname, + tgfoid::regproc, tgenabled + from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) + order by tgrelid::regclass::text, tgfoid; + tgrelid | tgname | tgfoid | tgenabled +---------+-------------------------+------------------------+----------- + child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D + child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D + parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D + parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D + parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D + parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D +(6 rows) + +drop table parent, child1; +-- Verify that firing state propagates correctly on creation, too +CREATE TABLE trgfire (i int) PARTITION BY RANGE (i); +CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10); +CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql + AS $$ begin raise exception 'except'; end $$; +CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf(); +INSERT INTO trgfire VALUES (1); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +ALTER TABLE trgfire DISABLE TRIGGER tg; +INSERT INTO trgfire VALUES (1); +CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20); +INSERT INTO trgfire VALUES (11); +CREATE TABLE trgfire3 (LIKE trgfire); +ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30); +INSERT INTO trgfire VALUES (21); +CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i); +CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30); +INSERT INTO trgfire VALUES (30); +CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i); +CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40); +ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50); +INSERT INTO trgfire VALUES (40); +SELECT tgrelid::regclass, tgenabled FROM pg_trigger + WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%') + ORDER BY tgrelid::regclass::text; + tgrelid | tgenabled +-------------+----------- + trgfire | D + trgfire1 | D + trgfire2 | D + trgfire3 | D + trgfire4 | D + trgfire4_30 | D + trgfire5 | D + trgfire5_40 | D +(8 rows) + +ALTER TABLE trgfire ENABLE TRIGGER tg; +INSERT INTO trgfire VALUES (1); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +INSERT INTO trgfire VALUES (11); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +INSERT INTO trgfire VALUES (21); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +INSERT INTO trgfire VALUES (30); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +INSERT INTO trgfire VALUES (40); +ERROR: except +CONTEXT: PL/pgSQL function tgf() line 1 at RAISE +DROP TABLE trgfire; +DROP FUNCTION tgf(); +-- +-- Test the interaction between transition tables and both kinds of +-- inheritance. We'll dump the contents of the transition tables in a +-- format that shows the attribute order, so that we can distinguish +-- tuple formats (though not dropped attributes). +-- +create or replace function dump_insert() returns trigger language plpgsql as +$$ + begin + raise notice 'trigger = %, new table = %', + TG_NAME, + (select string_agg(new_table::text, ', ' order by a) from new_table); + return null; + end; +$$; +create or replace function dump_update() returns trigger language plpgsql as +$$ + begin + raise notice 'trigger = %, old table = %, new table = %', + TG_NAME, + (select string_agg(old_table::text, ', ' order by a) from old_table), + (select string_agg(new_table::text, ', ' order by a) from new_table); + return null; + end; +$$; +create or replace function dump_delete() returns trigger language plpgsql as +$$ + begin + raise notice 'trigger = %, old table = %', + TG_NAME, + (select string_agg(old_table::text, ', ' order by a) from old_table); + return null; + end; +$$; +-- +-- Verify behavior of statement triggers on partition hierarchy with +-- transition tables. Tuples should appear to each trigger in the +-- format of the relation the trigger is attached to. +-- +-- set up a partition hierarchy with some different TupleDescriptors +create table parent (a text, b int) partition by list (a); +-- a child matching parent +create table child1 partition of parent for values in ('AAA'); +-- a child with a dropped column +create table child2 (x int, a text, b int); +alter table child2 drop column x; +alter table parent attach partition child2 for values in ('BBB'); +-- a child with a different column order +create table child3 (b int, a text); +alter table parent attach partition child3 for values in ('CCC'); +create trigger parent_insert_trig + after insert on parent referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger parent_update_trig + after update on parent referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger parent_delete_trig + after delete on parent referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child1_insert_trig + after insert on child1 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child1_update_trig + after update on child1 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child1_delete_trig + after delete on child1 referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child2_insert_trig + after insert on child2 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child2_update_trig + after update on child2 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child2_delete_trig + after delete on child2 referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child3_insert_trig + after insert on child3 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child3_update_trig + after update on child3 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child3_delete_trig + after delete on child3 referencing old table as old_table + for each statement execute procedure dump_delete(); +SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, + action_order, action_condition, action_orientation, action_timing, + action_reference_old_table, action_reference_new_table + FROM information_schema.triggers + WHERE event_object_table IN ('parent', 'child1', 'child2', 'child3') + ORDER BY trigger_name COLLATE "C", 2; + trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table +--------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- + child1_delete_trig | DELETE | public | child1 | 1 | | STATEMENT | AFTER | old_table | + child1_insert_trig | INSERT | public | child1 | 1 | | STATEMENT | AFTER | | new_table + child1_update_trig | UPDATE | public | child1 | 1 | | STATEMENT | AFTER | old_table | new_table + child2_delete_trig | DELETE | public | child2 | 1 | | STATEMENT | AFTER | old_table | + child2_insert_trig | INSERT | public | child2 | 1 | | STATEMENT | AFTER | | new_table + child2_update_trig | UPDATE | public | child2 | 1 | | STATEMENT | AFTER | old_table | new_table + child3_delete_trig | DELETE | public | child3 | 1 | | STATEMENT | AFTER | old_table | + child3_insert_trig | INSERT | public | child3 | 1 | | STATEMENT | AFTER | | new_table + child3_update_trig | UPDATE | public | child3 | 1 | | STATEMENT | AFTER | old_table | new_table + parent_delete_trig | DELETE | public | parent | 1 | | STATEMENT | AFTER | old_table | + parent_insert_trig | INSERT | public | parent | 1 | | STATEMENT | AFTER | | new_table + parent_update_trig | UPDATE | public | parent | 1 | | STATEMENT | AFTER | old_table | new_table +(12 rows) + +-- insert directly into children sees respective child-format tuples +insert into child1 values ('AAA', 42); +NOTICE: trigger = child1_insert_trig, new table = (AAA,42) +insert into child2 values ('BBB', 42); +NOTICE: trigger = child2_insert_trig, new table = (BBB,42) +insert into child3 values (42, 'CCC'); +NOTICE: trigger = child3_insert_trig, new table = (42,CCC) +-- update via parent sees parent-format tuples +update parent set b = b + 1; +NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) +-- delete via parent sees parent-format tuples +delete from parent; +NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) +-- insert into parent sees parent-format tuples +insert into parent values ('AAA', 42); +NOTICE: trigger = parent_insert_trig, new table = (AAA,42) +insert into parent values ('BBB', 42); +NOTICE: trigger = parent_insert_trig, new table = (BBB,42) +insert into parent values ('CCC', 42); +NOTICE: trigger = parent_insert_trig, new table = (CCC,42) +-- delete from children sees respective child-format tuples +delete from child1; +NOTICE: trigger = child1_delete_trig, old table = (AAA,42) +delete from child2; +NOTICE: trigger = child2_delete_trig, old table = (BBB,42) +delete from child3; +NOTICE: trigger = child3_delete_trig, old table = (42,CCC) +-- copy into parent sees parent-format tuples +copy parent (a, b) from stdin; +NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) +-- DML affecting parent sees tuples collected from children even if +-- there is no transition table trigger on the children +drop trigger child1_insert_trig on child1; +drop trigger child1_update_trig on child1; +drop trigger child1_delete_trig on child1; +drop trigger child2_insert_trig on child2; +drop trigger child2_update_trig on child2; +drop trigger child2_delete_trig on child2; +drop trigger child3_insert_trig on child3; +drop trigger child3_update_trig on child3; +drop trigger child3_delete_trig on child3; +delete from parent; +NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42) +-- copy into parent sees tuples collected from children even if there +-- is no transition-table trigger on the children +copy parent (a, b) from stdin; +NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) +-- insert into parent with a before trigger on a child tuple before +-- insertion, and we capture the newly modified row in parent format +create or replace function intercept_insert() returns trigger language plpgsql as +$$ + begin + new.b = new.b + 1000; + return new; + end; +$$; +create trigger intercept_insert_child3 + before insert on child3 + for each row execute procedure intercept_insert(); +-- insert, parent trigger sees post-modification parent-format tuple +insert into parent values ('AAA', 42), ('BBB', 42), ('CCC', 66); +NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1066) +-- copy, parent trigger sees post-modification parent-format tuple +copy parent (a, b) from stdin; +NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1234) +drop table child1, child2, child3, parent; +drop function intercept_insert(); +-- +-- Verify prohibition of row triggers with transition triggers on +-- partitions +-- +create table parent (a text, b int) partition by list (a); +create table child partition of parent for values in ('AAA'); +-- adding row trigger with transition table fails +create trigger child_row_trig + after insert on child referencing new table as new_table + for each row execute procedure dump_insert(); +ERROR: ROW triggers with transition tables are not supported on partitions +-- detaching it first works +alter table parent detach partition child; +create trigger child_row_trig + after insert on child referencing new table as new_table + for each row execute procedure dump_insert(); +-- but now we're not allowed to reattach it +alter table parent attach partition child for values in ('AAA'); +ERROR: trigger "child_row_trig" prevents table "child" from becoming a partition +DETAIL: ROW triggers with transition tables are not supported on partitions. +-- drop the trigger, and now we're allowed to attach it again +drop trigger child_row_trig on child; +alter table parent attach partition child for values in ('AAA'); +drop table child, parent; +-- +-- Verify behavior of statement triggers on (non-partition) +-- inheritance hierarchy with transition tables; similar to the +-- partition case, except there is no rerouting on insertion and child +-- tables can have extra columns +-- +-- set up inheritance hierarchy with different TupleDescriptors +create table parent (a text, b int); +-- a child matching parent +create table child1 () inherits (parent); +-- a child with a different column order +create table child2 (b int, a text); +alter table child2 inherit parent; +-- a child with an extra column +create table child3 (c text) inherits (parent); +create trigger parent_insert_trig + after insert on parent referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger parent_update_trig + after update on parent referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger parent_delete_trig + after delete on parent referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child1_insert_trig + after insert on child1 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child1_update_trig + after update on child1 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child1_delete_trig + after delete on child1 referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child2_insert_trig + after insert on child2 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child2_update_trig + after update on child2 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child2_delete_trig + after delete on child2 referencing old table as old_table + for each statement execute procedure dump_delete(); +create trigger child3_insert_trig + after insert on child3 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger child3_update_trig + after update on child3 referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger child3_delete_trig + after delete on child3 referencing old table as old_table + for each statement execute procedure dump_delete(); +-- insert directly into children sees respective child-format tuples +insert into child1 values ('AAA', 42); +NOTICE: trigger = child1_insert_trig, new table = (AAA,42) +insert into child2 values (42, 'BBB'); +NOTICE: trigger = child2_insert_trig, new table = (42,BBB) +insert into child3 values ('CCC', 42, 'foo'); +NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) +-- update via parent sees parent-format tuples +update parent set b = b + 1; +NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) +-- delete via parent sees parent-format tuples +delete from parent; +NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) +-- reinsert values into children for next test... +insert into child1 values ('AAA', 42); +NOTICE: trigger = child1_insert_trig, new table = (AAA,42) +insert into child2 values (42, 'BBB'); +NOTICE: trigger = child2_insert_trig, new table = (42,BBB) +insert into child3 values ('CCC', 42, 'foo'); +NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) +-- delete from children sees respective child-format tuples +delete from child1; +NOTICE: trigger = child1_delete_trig, old table = (AAA,42) +delete from child2; +NOTICE: trigger = child2_delete_trig, old table = (42,BBB) +delete from child3; +NOTICE: trigger = child3_delete_trig, old table = (CCC,42,foo) +-- copy into parent sees parent-format tuples (no rerouting, so these +-- are really inserted into the parent) +copy parent (a, b) from stdin; +NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) +-- same behavior for copy if there is an index (interesting because rows are +-- captured by a different code path in copyfrom.c if there are indexes) +create index on parent(b); +copy parent (a, b) from stdin; +NOTICE: trigger = parent_insert_trig, new table = (DDD,42) +-- DML affecting parent sees tuples collected from children even if +-- there is no transition table trigger on the children +drop trigger child1_insert_trig on child1; +drop trigger child1_update_trig on child1; +drop trigger child1_delete_trig on child1; +drop trigger child2_insert_trig on child2; +drop trigger child2_update_trig on child2; +drop trigger child2_delete_trig on child2; +drop trigger child3_insert_trig on child3; +drop trigger child3_update_trig on child3; +drop trigger child3_delete_trig on child3; +delete from parent; +NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42), (DDD,42) +drop table child1, child2, child3, parent; +-- +-- Verify prohibition of row triggers with transition triggers on +-- inheritance children +-- +create table parent (a text, b int); +create table child () inherits (parent); +-- adding row trigger with transition table fails +create trigger child_row_trig + after insert on child referencing new table as new_table + for each row execute procedure dump_insert(); +ERROR: ROW triggers with transition tables are not supported on inheritance children +-- disinheriting it first works +alter table child no inherit parent; +create trigger child_row_trig + after insert on child referencing new table as new_table + for each row execute procedure dump_insert(); +-- but now we're not allowed to make it inherit anymore +alter table child inherit parent; +ERROR: trigger "child_row_trig" prevents table "child" from becoming an inheritance child +DETAIL: ROW triggers with transition tables are not supported in inheritance hierarchies. +-- drop the trigger, and now we're allowed to make it inherit again +drop trigger child_row_trig on child; +alter table child inherit parent; +drop table child, parent; +-- +-- Verify behavior of queries with wCTEs, where multiple transition +-- tuplestores can be active at the same time because there are +-- multiple DML statements that might fire triggers with transition +-- tables +-- +create table table1 (a int); +create table table2 (a text); +create trigger table1_trig + after insert on table1 referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger table2_trig + after insert on table2 referencing new table as new_table + for each statement execute procedure dump_insert(); +with wcte as (insert into table1 values (42)) + insert into table2 values ('hello world'); +NOTICE: trigger = table2_trig, new table = ("hello world") +NOTICE: trigger = table1_trig, new table = (42) +with wcte as (insert into table1 values (43)) + insert into table1 values (44); +NOTICE: trigger = table1_trig, new table = (43), (44) +select * from table1; + a +---- + 42 + 44 + 43 +(3 rows) + +select * from table2; + a +------------- + hello world +(1 row) + +drop table table1; +drop table table2; +-- +-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with +-- transition tables. +-- +create table my_table (a int primary key, b text); +create trigger my_table_insert_trig + after insert on my_table referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger my_table_update_trig + after update on my_table referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +-- inserts only +insert into my_table values (1, 'AAA'), (2, 'BBB') + on conflict (a) do + update set b = my_table.b || ':' || excluded.b; +NOTICE: trigger = my_table_update_trig, old table = <NULL>, new table = <NULL> +NOTICE: trigger = my_table_insert_trig, new table = (1,AAA), (2,BBB) +-- mixture of inserts and updates +insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = my_table.b || ':' || excluded.b; +NOTICE: trigger = my_table_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB) +NOTICE: trigger = my_table_insert_trig, new table = (3,CCC), (4,DDD) +-- updates only +insert into my_table values (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = my_table.b || ':' || excluded.b; +NOTICE: trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) +NOTICE: trigger = my_table_insert_trig, new table = <NULL> +-- +-- now using a partitioned table +-- +create table iocdu_tt_parted (a int primary key, b text) partition by list (a); +create table iocdu_tt_parted1 partition of iocdu_tt_parted for values in (1); +create table iocdu_tt_parted2 partition of iocdu_tt_parted for values in (2); +create table iocdu_tt_parted3 partition of iocdu_tt_parted for values in (3); +create table iocdu_tt_parted4 partition of iocdu_tt_parted for values in (4); +create trigger iocdu_tt_parted_insert_trig + after insert on iocdu_tt_parted referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger iocdu_tt_parted_update_trig + after update on iocdu_tt_parted referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +-- inserts only +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +NOTICE: trigger = iocdu_tt_parted_update_trig, old table = <NULL>, new table = <NULL> +NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (1,AAA), (2,BBB) +-- mixture of inserts and updates +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +NOTICE: trigger = iocdu_tt_parted_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB) +NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (3,CCC), (4,DDD) +-- updates only +insert into iocdu_tt_parted values (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +NOTICE: trigger = iocdu_tt_parted_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) +NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = <NULL> +drop table iocdu_tt_parted; +-- +-- Verify that you can't create a trigger with transition tables for +-- more than one event. +-- +create trigger my_table_multievent_trig + after insert or update on my_table referencing new table as new_table + for each statement execute procedure dump_insert(); +ERROR: transition tables cannot be specified for triggers with more than one event +-- +-- Verify that you can't create a trigger with transition tables with +-- a column list. +-- +create trigger my_table_col_update_trig + after update of b on my_table referencing new table as new_table + for each statement execute procedure dump_insert(); +ERROR: transition tables cannot be specified for triggers with column lists +drop table my_table; +-- +-- Test firing of triggers with transition tables by foreign key cascades +-- +create table refd_table (a int primary key, b text); +create table trig_table (a int, b text, + foreign key (a) references refd_table on update cascade on delete cascade +); +create trigger trig_table_before_trig + before insert or update or delete on trig_table + for each statement execute procedure trigger_func('trig_table'); +create trigger trig_table_insert_trig + after insert on trig_table referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger trig_table_update_trig + after update on trig_table referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger trig_table_delete_trig + after delete on trig_table referencing old table as old_table + for each statement execute procedure dump_delete(); +insert into refd_table values + (1, 'one'), + (2, 'two'), + (3, 'three'); +insert into trig_table values + (1, 'one a'), + (1, 'one b'), + (2, 'two a'), + (2, 'two b'), + (3, 'three a'), + (3, 'three b'); +NOTICE: trigger_func(trig_table) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger = trig_table_insert_trig, new table = (1,"one a"), (1,"one b"), (2,"two a"), (2,"two b"), (3,"three a"), (3,"three b") +update refd_table set a = 11 where b = 'one'; +NOTICE: trigger_func(trig_table) called: action = UPDATE, when = BEFORE, level = STATEMENT +NOTICE: trigger = trig_table_update_trig, old table = (1,"one a"), (1,"one b"), new table = (11,"one a"), (11,"one b") +select * from trig_table; + a | b +----+--------- + 2 | two a + 2 | two b + 3 | three a + 3 | three b + 11 | one a + 11 | one b +(6 rows) + +delete from refd_table where length(b) = 3; +NOTICE: trigger_func(trig_table) called: action = DELETE, when = BEFORE, level = STATEMENT +NOTICE: trigger = trig_table_delete_trig, old table = (2,"two a"), (2,"two b"), (11,"one a"), (11,"one b") +select * from trig_table; + a | b +---+--------- + 3 | three a + 3 | three b +(2 rows) + +drop table refd_table, trig_table; +-- +-- self-referential FKs are even more fun +-- +create table self_ref (a int primary key, + b int references self_ref(a) on delete cascade); +create trigger self_ref_before_trig + before delete on self_ref + for each statement execute procedure trigger_func('self_ref'); +create trigger self_ref_r_trig + after delete on self_ref referencing old table as old_table + for each row execute procedure dump_delete(); +create trigger self_ref_s_trig + after delete on self_ref referencing old table as old_table + for each statement execute procedure dump_delete(); +insert into self_ref values (1, null), (2, 1), (3, 2); +delete from self_ref where a = 1; +NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT +NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) +NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT +NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) +NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1) +NOTICE: trigger = self_ref_r_trig, old table = (3,2) +NOTICE: trigger = self_ref_s_trig, old table = (3,2) +-- without AR trigger, cascaded deletes all end up in one transition table +drop trigger self_ref_r_trig on self_ref; +insert into self_ref values (1, null), (2, 1), (3, 2), (4, 3); +delete from self_ref where a = 1; +NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT +NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1), (3,2), (4,3) +drop table self_ref; +-- +-- test transition tables with MERGE +-- +create table merge_target_table (a int primary key, b text); +create trigger merge_target_table_insert_trig + after insert on merge_target_table referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger merge_target_table_update_trig + after update on merge_target_table referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +create trigger merge_target_table_delete_trig + after delete on merge_target_table referencing old table as old_table + for each statement execute procedure dump_delete(); +create table merge_source_table (a int, b text); +insert into merge_source_table + values (1, 'initial1'), (2, 'initial2'), + (3, 'initial3'), (4, 'initial4'); +merge into merge_target_table t +using merge_source_table s +on t.a = s.a +when not matched then + insert values (a, b); +NOTICE: trigger = merge_target_table_insert_trig, new table = (1,initial1), (2,initial2), (3,initial3), (4,initial4) +merge into merge_target_table t +using merge_source_table s +on t.a = s.a +when matched and s.a <= 2 then + update set b = t.b || ' updated by merge' +when matched and s.a > 2 then + delete +when not matched then + insert values (a, b); +NOTICE: trigger = merge_target_table_delete_trig, old table = (3,initial3), (4,initial4) +NOTICE: trigger = merge_target_table_update_trig, old table = (1,initial1), (2,initial2), new table = (1,"initial1 updated by merge"), (2,"initial2 updated by merge") +NOTICE: trigger = merge_target_table_insert_trig, new table = <NULL> +merge into merge_target_table t +using merge_source_table s +on t.a = s.a +when matched and s.a <= 2 then + update set b = t.b || ' updated again by merge' +when matched and s.a > 2 then + delete +when not matched then + insert values (a, b); +NOTICE: trigger = merge_target_table_delete_trig, old table = <NULL> +NOTICE: trigger = merge_target_table_update_trig, old table = (1,"initial1 updated by merge"), (2,"initial2 updated by merge"), new table = (1,"initial1 updated by merge updated again by merge"), (2,"initial2 updated by merge updated again by merge") +NOTICE: trigger = merge_target_table_insert_trig, new table = (3,initial3), (4,initial4) +drop table merge_source_table, merge_target_table; +-- cleanup +drop function dump_insert(); +drop function dump_update(); +drop function dump_delete(); +-- +-- Tests for CREATE OR REPLACE TRIGGER +-- +create table my_table (id integer); +create function funcA() returns trigger as $$ +begin + raise notice 'hello from funcA'; + return null; +end; $$ language plpgsql; +create function funcB() returns trigger as $$ +begin + raise notice 'hello from funcB'; + return null; +end; $$ language plpgsql; +create trigger my_trig + after insert on my_table + for each row execute procedure funcA(); +create trigger my_trig + before insert on my_table + for each row execute procedure funcB(); -- should fail +ERROR: trigger "my_trig" for relation "my_table" already exists +insert into my_table values (1); +NOTICE: hello from funcA +create or replace trigger my_trig + before insert on my_table + for each row execute procedure funcB(); -- OK +insert into my_table values (2); -- this insert should become a no-op +NOTICE: hello from funcB +table my_table; + id +---- + 1 +(1 row) + +drop table my_table; +-- test CREATE OR REPLACE TRIGGER on partition table +create table parted_trig (a int) partition by range (a); +create table parted_trig_1 partition of parted_trig + for values from (0) to (1000) partition by range (a); +create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100); +create table parted_trig_2 partition of parted_trig for values from (1000) to (2000); +create table default_parted_trig partition of parted_trig default; +-- test that trigger can be replaced by another one +-- at the same level of partition table +create or replace trigger my_trig + after insert on parted_trig + for each row execute procedure funcA(); +insert into parted_trig (a) values (50); +NOTICE: hello from funcA +create or replace trigger my_trig + after insert on parted_trig + for each row execute procedure funcB(); +insert into parted_trig (a) values (50); +NOTICE: hello from funcB +-- test that child trigger cannot be replaced directly +create or replace trigger my_trig + after insert on parted_trig + for each row execute procedure funcA(); +insert into parted_trig (a) values (50); +NOTICE: hello from funcA +create or replace trigger my_trig + after insert on parted_trig_1 + for each row execute procedure funcB(); -- should fail +ERROR: trigger "my_trig" for relation "parted_trig_1" is an internal or a child trigger +insert into parted_trig (a) values (50); +NOTICE: hello from funcA +drop trigger my_trig on parted_trig; +insert into parted_trig (a) values (50); +-- test that user trigger can be overwritten by one defined at upper level +create trigger my_trig + after insert on parted_trig_1 + for each row execute procedure funcA(); +insert into parted_trig (a) values (50); +NOTICE: hello from funcA +create trigger my_trig + after insert on parted_trig + for each row execute procedure funcB(); -- should fail +ERROR: trigger "my_trig" for relation "parted_trig_1" already exists +insert into parted_trig (a) values (50); +NOTICE: hello from funcA +create or replace trigger my_trig + after insert on parted_trig + for each row execute procedure funcB(); +insert into parted_trig (a) values (50); +NOTICE: hello from funcB +-- cleanup +drop table parted_trig; +drop function funcA(); +drop function funcB(); +-- Leave around some objects for other tests +create table trigger_parted (a int primary key) partition by list (a); +create function trigger_parted_trigfunc() returns trigger language plpgsql as + $$ begin end; $$; +create trigger aft_row after insert or update on trigger_parted + for each row execute function trigger_parted_trigfunc(); +create table trigger_parted_p1 partition of trigger_parted for values in (1) + partition by list (a); +create table trigger_parted_p1_1 partition of trigger_parted_p1 for values in (1); +create table trigger_parted_p2 partition of trigger_parted for values in (2) + partition by list (a); +create table trigger_parted_p2_2 partition of trigger_parted_p2 for values in (2); +alter table only trigger_parted_p2 disable trigger aft_row; +alter table trigger_parted_p2_2 enable always trigger aft_row; +-- verify transition table conversion slot's lifetime +-- https://postgr.es/m/39a71864-b120-5a5c-8cc5-c632b6f16761@amazon.com +create table convslot_test_parent (col1 text primary key); +create table convslot_test_child (col1 text primary key, + foreign key (col1) references convslot_test_parent(col1) on delete cascade on update cascade +); +alter table convslot_test_child add column col2 text not null default 'tutu'; +insert into convslot_test_parent(col1) values ('1'); +insert into convslot_test_child(col1) values ('1'); +insert into convslot_test_parent(col1) values ('3'); +insert into convslot_test_child(col1) values ('3'); +create function convslot_trig1() +returns trigger +language plpgsql +AS $$ +begin +raise notice 'trigger = %, old_table = %', + TG_NAME, + (select string_agg(old_table::text, ', ' order by col1) from old_table); +return null; +end; $$; +create function convslot_trig2() +returns trigger +language plpgsql +AS $$ +begin +raise notice 'trigger = %, new table = %', + TG_NAME, + (select string_agg(new_table::text, ', ' order by col1) from new_table); +return null; +end; $$; +create trigger but_trigger after update on convslot_test_child +referencing new table as new_table +for each statement execute function convslot_trig2(); +update convslot_test_parent set col1 = col1 || '1'; +NOTICE: trigger = but_trigger, new table = (11,tutu), (31,tutu) +create function convslot_trig3() +returns trigger +language plpgsql +AS $$ +begin +raise notice 'trigger = %, old_table = %, new table = %', + TG_NAME, + (select string_agg(old_table::text, ', ' order by col1) from old_table), + (select string_agg(new_table::text, ', ' order by col1) from new_table); +return null; +end; $$; +create trigger but_trigger2 after update on convslot_test_child +referencing old table as old_table new table as new_table +for each statement execute function convslot_trig3(); +update convslot_test_parent set col1 = col1 || '1'; +NOTICE: trigger = but_trigger, new table = (111,tutu), (311,tutu) +NOTICE: trigger = but_trigger2, old_table = (11,tutu), (31,tutu), new table = (111,tutu), (311,tutu) +create trigger bdt_trigger after delete on convslot_test_child +referencing old table as old_table +for each statement execute function convslot_trig1(); +delete from convslot_test_parent; +NOTICE: trigger = bdt_trigger, old_table = (111,tutu), (311,tutu) +drop table convslot_test_child, convslot_test_parent; +drop function convslot_trig1(); +drop function convslot_trig2(); +drop function convslot_trig3(); +-- Bug #17607: variant of above in which trigger function raises an error; +-- we don't see any ill effects unless trigger tuple requires mapping +create table convslot_test_parent (id int primary key, val int) +partition by range (id); +create table convslot_test_part (val int, id int not null); +alter table convslot_test_parent + attach partition convslot_test_part for values from (1) to (1000); +create function convslot_trig4() returns trigger as +$$begin raise exception 'BOOM!'; end$$ language plpgsql; +create trigger convslot_test_parent_update + after update on convslot_test_parent + referencing old table as old_rows new table as new_rows + for each statement execute procedure convslot_trig4(); +insert into convslot_test_parent (id, val) values (1, 2); +begin; +savepoint svp; +update convslot_test_parent set val = 3; -- error expected +ERROR: BOOM! +CONTEXT: PL/pgSQL function convslot_trig4() line 1 at RAISE +rollback to savepoint svp; +rollback; +drop table convslot_test_parent; +drop function convslot_trig4(); +-- Test trigger renaming on partitioned tables +create table grandparent (id int, primary key (id)) partition by range (id); +create table middle partition of grandparent for values from (1) to (10) +partition by range (id); +create table chi partition of middle for values from (1) to (5); +create table cho partition of middle for values from (6) to (10); +create function f () returns trigger as +$$ begin return new; end; $$ +language plpgsql; +create trigger a after insert on grandparent +for each row execute procedure f(); +alter trigger a on grandparent rename to b; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text COLLATE "C"; + tgrelid | tgname | parent_tgname +-------------+--------+--------------- + chi | b | b + cho | b | b + grandparent | b | + middle | b | b +(4 rows) + +alter trigger a on only grandparent rename to b; -- ONLY not supported +ERROR: syntax error at or near "only" +LINE 1: alter trigger a on only grandparent rename to b; + ^ +alter trigger b on middle rename to c; -- can't rename trigger on partition +ERROR: cannot rename trigger "b" on table "middle" +HINT: Rename the trigger on the partitioned table "grandparent" instead. +create trigger c after insert on middle +for each row execute procedure f(); +alter trigger b on grandparent rename to c; +ERROR: trigger "c" for relation "middle" already exists +-- Rename cascading does not affect statement triggers +create trigger p after insert on grandparent for each statement execute function f(); +create trigger p after insert on middle for each statement execute function f(); +alter trigger p on grandparent rename to q; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text COLLATE "C"; + tgrelid | tgname | parent_tgname +-------------+--------+--------------- + chi | b | b + cho | b | b + grandparent | b | + middle | b | b + chi | c | c + cho | c | c + middle | c | + middle | p | + grandparent | q | +(9 rows) + +drop table grandparent; +-- Trigger renaming does not recurse on legacy inheritance +create table parent (a int); +create table child () inherits (parent); +create trigger parenttrig after insert on parent +for each row execute procedure f(); +create trigger parenttrig after insert on child +for each row execute procedure f(); +alter trigger parenttrig on parent rename to anothertrig; +\d+ child + Table "public.child" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | +Triggers: + parenttrig AFTER INSERT ON child FOR EACH ROW EXECUTE FUNCTION f() +Inherits: parent + +drop table parent, child; +drop function f(); |