diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index a599205..d41fa32 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -100,6 +105,10 @@ List *recheckIndexes, Bitmapset *modifiedCols, TransitionCaptureState *transition_capture); static void AfterTriggerEnlargeQueryState(void); +static Trigger *PrioritizeConstraintTriggers(Trigger* triggers, + int numtrigs, + int numInternalConstraints, + int numCustomConstraints); /* @@ -1642,6 +1647,59 @@ CacheInvalidateRelcache(rel); } +/* + * Given an array of triggers sorted by name, returns a new array of triggers + * that prioritizes first internal constraint triggers, then user constraint + * triggers. + * + * Note: Constraints will just take precedence over normal triggers, and + * internal constraints will take the highest precedence. Each partition + * of the array will preserve the existing order of triggers as passed in. + * This will return a new array that is a modified version of the array + * passed in. + */ +static Trigger *PrioritizeConstraintTriggers(Trigger* triggers, + int numtrigs, + int numInternalConstraints, + int numCustomConstraints) +{ + Trigger *result; + int i; + int internalConstStart = 0; + int customConstStart = numInternalConstraints; + int normalTriggerStart = numInternalConstraints + numCustomConstraints; + int internalConstOffset = 0; + int customConstOffset = 0; + int normalTriggerOffset = 0; + + result = palloc(numtrigs * sizeof(Trigger)); + + for (i = 0; i < numtrigs; i++) + { + int sortIndex; + + if (triggers[i].tgconstraint != 0) + { + if (triggers[i].tgisinternal) + { + sortIndex = internalConstStart + internalConstOffset; + internalConstOffset++; + } + else + { + sortIndex = customConstStart + customConstOffset; + customConstOffset++; + } + } + else + { + sortIndex = normalTriggerStart + normalTriggerOffset; + normalTriggerOffset++; + } + result[sortIndex] = triggers[i]; + } + return result; +} /* * Build trigger data to attach to the given relcache entry. @@ -1658,8 +1665,11 @@ { TriggerDesc *trigdesc; int numtrigs; + int numInternalConstraints; + int numCustomConstraints; int maxtrigs; Trigger *triggers; + Trigger *prioritized_trigs; Relation tgrel; ScanKeyData skey; SysScanDesc tgscan; @@ -1674,6 +1679,8 @@ maxtrigs = 16; triggers = (Trigger *) palloc(maxtrigs * sizeof(Trigger)); numtrigs = 0; + numInternalConstraints = 0; + numCustomConstraints = 0; /* * Note: since we scan the triggers using TriggerRelidNameIndexId, we will @@ -1703,6 +1708,14 @@ triggers = (Trigger *) repalloc(triggers, maxtrigs * sizeof(Trigger)); } build = &(triggers[numtrigs]); + + if (pg_trigger->tgconstraint != 0) + { + if (pg_trigger->tgisinternal) + numInternalConstraints++; + else + numCustomConstraints++; + } build->tgoid = HeapTupleGetOid(htup); build->tgname = DatumGetCString(DirectFunctionCall1(nameout, @@ -1785,12 +1796,16 @@ return; } + prioritized_trigs = PrioritizeConstraintTriggers(triggers, numtrigs, + numInternalConstraints, numCustomConstraints); + pfree(triggers); + /* Build trigdesc */ trigdesc = (TriggerDesc *) palloc0(sizeof(TriggerDesc)); - trigdesc->triggers = triggers; + trigdesc->triggers = prioritized_trigs; trigdesc->numtriggers = numtrigs; for (i = 0; i < numtrigs; i++) - SetTriggerFlags(trigdesc, &(triggers[i])); + SetTriggerFlags(trigdesc, &(prioritized_trigs[i])); /* Copy completed trigdesc into cache storage */ oldContext = MemoryContextSwitchTo(CacheMemoryContext); diff --git a/src/test/regress/expected/man-5377.out b/src/test/regress/expected/man-5377.out new file mode 100644 index 0000000..d2e7a81 --- /dev/null +++ b/src/test/regress/expected/man-5377.out @@ -0,0 +0,244 @@ +create schema ts; +create table ts.t1 (id int primary key); +create table ts.t2 (id int primary key); +create table ts.t3 (id int primary key, t1_id int, t2_id int, + foreign key (t1_id) references ts.t1(id), + foreign key (t2_id) references ts.t2(id)); +insert into ts.t1 select generate_series(1,10) as id; +insert into ts.t2 values(0); +insert into ts.t3 select generate_series(11,20) as id, + generate_series(1,10) as t1_id; +create or replace function "A_trig" () returns trigger +as $$ +begin + raise notice 'A_trig fired!'; + return null; +end; +$$ language plpgsql; +create or replace function "D_trig" () returns trigger +as $$ +begin + raise notice 'D_trig fired!'; + return null; +end; +$$ language plpgsql; +create or replace function "B_constr" () returns trigger +as $$ +begin + raise notice 'B_constr fired!'; + return null; +end; +$$ language plpgsql; +create or replace function "C_constr" () returns trigger +as $$ +begin + raise notice 'C_constr fired!'; + return null; +end; +$$ language plpgsql; +-- Test 001: TestWhenUserTriggerDefinedThenImmediateInternalConstrFiresFirst +begin; +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- A_trig should not fire because Fkey constraint will fire first +-- and error out +insert into ts.t3 values(21, 11, 0); +ERROR: insert or update on table "t3" violates foreign key constraint "t3_t1_id_fkey" +DETAIL: Key (t1_id)=(11) is not present in table "t1". +rollback; +-- Test 002: TestWhenUserTriggerDefinedThenImmediateCustomConstrFiresFirst +begin; +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); +create constraint trigger "B_constr" after insert on ts.t1 + not deferrable for each row execute procedure "B_constr"(); +-- expect B_constr->A_trig +insert into ts.t1 values(11); +NOTICE: B_constr fired! +NOTICE: A_trig fired! +rollback; +-- Test 003: TestWhenCustomConstrDefinedThenImmediateInternalCosntrFiresFirst +begin; +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +-- B_constr should not fire because Fkey constraint will fire first +-- and error out +insert into ts.t3 values(21, 11, 0); +ERROR: insert or update on table "t3" violates foreign key constraint "t3_t1_id_fkey" +DETAIL: Key (t1_id)=(11) is not present in table "t1". +rollback; +-- Test 004: TestWhenAllDefinedThenFireInProperOrder +begin; +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- B_constr and A_trig should not fire because Fkey constraint will fire first +-- and error out. Use this to test internal is firing first when all defined +insert into ts.t3 values(21, 11, 0); +ERROR: insert or update on table "t3" violates foreign key constraint "t3_t1_id_fkey" +DETAIL: Key (t1_id)=(11) is not present in table "t1". +rollback; +begin; +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- Now Fkey constraint will pass, then B_constr should fire, followed by A_trig +insert into ts.t3 values(21, 10, 0); +NOTICE: B_constr fired! +NOTICE: A_trig fired! +rollback; +-- Test 005: TestWhenInternalConstrDeferredThenTriggersFireFirst +begin; +alter table ts.t3 alter constraint t3_t1_id_fkey deferrable; +set constraints ts.t3_t1_id_fkey deferred; +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- A_trig should fire +insert into ts.t3 values(100, 100, 0); +NOTICE: A_trig fired! +-- fix constraints +insert into ts.t1 values(100); +-- fkey constraint should pass on commit +commit; +drop trigger "A_trig" on ts.t3; +-- Test 006: TestWhenCustomConstrDeferredThenTriggersFireFirst +begin; +create constraint trigger "B_constr" after insert on ts.t1 + deferrable initially deferred for each row execute procedure "B_constr"(); +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); +-- A_trig should fire +insert into ts.t1 values(101); +NOTICE: A_trig fired! +-- B_constr should fire on transaction commit +commit; +NOTICE: B_constr fired! +drop trigger "B_constr" on ts.t1; +drop trigger "A_trig" on ts.t1; +-- Test 007: TestWhenInternalAndCustomConstrDeferredThenTriggersFireFirst +begin; +alter table ts.t3 alter constraint t3_t1_id_fkey deferrable; +set constraints ts.t3_t1_id_fkey deferred; +create constraint trigger "B_constr" after insert on ts.t3 + deferrable initially deferred for each row execute procedure "B_constr"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- A_trig should fire first +insert into ts.t3 values(102, 102, 0); +NOTICE: A_trig fired! +-- fix fkey constraint +insert into ts.t1 values(102); +-- fkey constraint should fire and pass, followed by B_constr +commit; +NOTICE: B_constr fired! +drop trigger "B_constr" on ts.t3; +drop trigger "A_trig" on ts.t3; +-- Test 008: TestWhenMultipleCustomConstrThenConstrFireInAlphabeticalOrder +begin; +create constraint trigger "C_constr" after insert on ts.t1 + not deferrable for each row execute procedure "C_constr"(); +create constraint trigger "B_constr" after insert on ts.t1 + not deferrable for each row execute procedure "B_constr"(); +-- B_constr -> C_constr expected +insert into ts.t1 values(11); +NOTICE: B_constr fired! +NOTICE: C_constr fired! +rollback; +-- Test 009: TestWhenMultipleCustomTriggersThenTriggersFireInAlphabeticalOrder +begin; +create trigger "D_trig" after insert on ts.t1 + for each row execute procedure "D_trig"(); +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); +-- A_trig -> D_trig expected +insert into ts.t1 values(11); +NOTICE: A_trig fired! +NOTICE: D_trig fired! +rollback; +-- Test 010: TestWhenMultipleOfEachThenFireInProperOrderEachAlphabetically +begin; +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- Check that FKey fires first, this should fail because insert violates +-- the constraint +insert into ts.t3 values(21, 11, 0); +ERROR: insert or update on table "t3" violates foreign key constraint "t3_t1_id_fkey" +DETAIL: Key (t1_id)=(11) is not present in table "t1". +rollback; +begin; +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- Now Fkey constraint will pass, then B_constr->C_constr->A_trig->D_trig +insert into ts.t3 values(21, 10); +NOTICE: B_constr fired! +NOTICE: C_constr fired! +NOTICE: A_trig fired! +NOTICE: D_trig fired! +rollback; +-- Test 011: TestWhenOneConstrDeferredThenOtherConstrsFireFirst +begin; +create constraint trigger "C_constr" after insert on ts.t1 + not deferrable for each row execute procedure "C_constr"(); +create constraint trigger "B_constr" after insert on ts.t1 + deferrable initially deferred for each row execute procedure "B_constr"(); +create trigger "D_trig" after insert on ts.t1 + for each row execute procedure "D_trig"(); +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); +-- This should run non def constr C_constr. Then A_trig->D_trig. +insert into ts.t1 values(103); +NOTICE: C_constr fired! +NOTICE: A_trig fired! +NOTICE: D_trig fired! +-- On transaction commmit run B_constr +commit; +NOTICE: B_constr fired! +drop trigger "B_constr" on ts.t1; +drop trigger "C_constr" on ts.t1; +drop trigger "A_trig" on ts.t1; +drop trigger "D_trig" on ts.t1; +-- Test 012: TestWhenInternalConstrDeferredThenCheckedAtTransactionCommit +begin; +-- defers the fkey from t2 to t3 +alter table ts.t3 alter constraint t3_t2_id_fkey deferrable; +set constraints ts.t3_t2_id_fkey deferred; +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); +-- Should run all of the other triggers, including the fkey to t1 +-- which it will check first and should pass. +insert into ts.t3 values(21, 10, 1); +NOTICE: B_constr fired! +NOTICE: C_constr fired! +NOTICE: A_trig fired! +NOTICE: D_trig fired! +-- transaction commit should fail because of fkey constraint to t3 +-- transaction should abort now +commit; +ERROR: insert or update on table "t3" violates foreign key constraint "t3_t2_id_fkey" +DETAIL: Key (t2_id)=(1) is not present in table "t2". +-- Clean up +drop schema ts cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table ts.t1 +drop cascades to table ts.t2 +drop cascades to table ts.t3 diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index cfb20a8..85e148c 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -10,6 +15,7 @@ test: man-2279 test: man-1941 test: man-784 +test: man-5377 test: tablespace test: boolean test: char diff --git a/src/test/regress/sql/man-5377.sql b/src/test/regress/sql/man-5377.sql new file mode 100644 index 0000000..05a4117 --- /dev/null +++ b/src/test/regress/sql/man-5377.sql @@ -0,0 +0,280 @@ +create schema ts; + +create table ts.t1 (id int primary key); +create table ts.t2 (id int primary key); +create table ts.t3 (id int primary key, t1_id int, t2_id int, + foreign key (t1_id) references ts.t1(id), + foreign key (t2_id) references ts.t2(id)); + +insert into ts.t1 select generate_series(1,10) as id; +insert into ts.t2 values(0); +insert into ts.t3 select generate_series(11,20) as id, + generate_series(1,10) as t1_id; + +create or replace function "A_trig" () returns trigger +as $$ +begin + raise notice 'A_trig fired!'; + return null; +end; +$$ language plpgsql; + +create or replace function "D_trig" () returns trigger +as $$ +begin + raise notice 'D_trig fired!'; + return null; +end; +$$ language plpgsql; + +create or replace function "B_constr" () returns trigger +as $$ +begin + raise notice 'B_constr fired!'; + return null; +end; +$$ language plpgsql; + +create or replace function "C_constr" () returns trigger +as $$ +begin + raise notice 'C_constr fired!'; + return null; +end; +$$ language plpgsql; + +-- Test 001: TestWhenUserTriggerDefinedThenImmediateInternalConstrFiresFirst +begin; + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- A_trig should not fire because Fkey constraint will fire first +-- and error out +insert into ts.t3 values(21, 11, 0); +rollback; + +-- Test 002: TestWhenUserTriggerDefinedThenImmediateCustomConstrFiresFirst +begin; + +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); + +create constraint trigger "B_constr" after insert on ts.t1 + not deferrable for each row execute procedure "B_constr"(); + +-- expect B_constr->A_trig +insert into ts.t1 values(11); +rollback; + +-- Test 003: TestWhenCustomConstrDefinedThenImmediateInternalCosntrFiresFirst +begin; + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +-- B_constr should not fire because Fkey constraint will fire first +-- and error out +insert into ts.t3 values(21, 11, 0); +rollback; + +-- Test 004: TestWhenAllDefinedThenFireInProperOrder +begin; + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- B_constr and A_trig should not fire because Fkey constraint will fire first +-- and error out. Use this to test internal is firing first when all defined +insert into ts.t3 values(21, 11, 0); +rollback; + +begin; + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- Now Fkey constraint will pass, then B_constr should fire, followed by A_trig +insert into ts.t3 values(21, 10, 0); +rollback; + +-- Test 005: TestWhenInternalConstrDeferredThenTriggersFireFirst +begin; + +alter table ts.t3 alter constraint t3_t1_id_fkey deferrable; +set constraints ts.t3_t1_id_fkey deferred; + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- A_trig should fire +insert into ts.t3 values(100, 100, 0); +-- fix constraints +insert into ts.t1 values(100); +-- fkey constraint should pass on commit +commit; + +drop trigger "A_trig" on ts.t3; + +-- Test 006: TestWhenCustomConstrDeferredThenTriggersFireFirst +begin; + +create constraint trigger "B_constr" after insert on ts.t1 + deferrable initially deferred for each row execute procedure "B_constr"(); + +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); + +-- A_trig should fire +insert into ts.t1 values(101); +-- B_constr should fire on transaction commit +commit; + +drop trigger "B_constr" on ts.t1; +drop trigger "A_trig" on ts.t1; + +-- Test 007: TestWhenInternalAndCustomConstrDeferredThenTriggersFireFirst +begin; + +alter table ts.t3 alter constraint t3_t1_id_fkey deferrable; +set constraints ts.t3_t1_id_fkey deferred; + +create constraint trigger "B_constr" after insert on ts.t3 + deferrable initially deferred for each row execute procedure "B_constr"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- A_trig should fire first +insert into ts.t3 values(102, 102, 0); +-- fix fkey constraint +insert into ts.t1 values(102); +-- fkey constraint should fire and pass, followed by B_constr +commit; + +drop trigger "B_constr" on ts.t3; +drop trigger "A_trig" on ts.t3; + +-- Test 008: TestWhenMultipleCustomConstrThenConstrFireInAlphabeticalOrder +begin; + +create constraint trigger "C_constr" after insert on ts.t1 + not deferrable for each row execute procedure "C_constr"(); + +create constraint trigger "B_constr" after insert on ts.t1 + not deferrable for each row execute procedure "B_constr"(); + +-- B_constr -> C_constr expected +insert into ts.t1 values(11); +rollback; + +-- Test 009: TestWhenMultipleCustomTriggersThenTriggersFireInAlphabeticalOrder +begin; + +create trigger "D_trig" after insert on ts.t1 + for each row execute procedure "D_trig"(); + +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); + +-- A_trig -> D_trig expected +insert into ts.t1 values(11); +rollback; + +-- Test 010: TestWhenMultipleOfEachThenFireInProperOrderEachAlphabetically +begin; + +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- Check that FKey fires first, this should fail because insert violates +-- the constraint +insert into ts.t3 values(21, 11, 0); +rollback; + +begin; + +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- Now Fkey constraint will pass, then B_constr->C_constr->A_trig->D_trig +insert into ts.t3 values(21, 10); +rollback; + +-- Test 011: TestWhenOneConstrDeferredThenOtherConstrsFireFirst +begin; + +create constraint trigger "C_constr" after insert on ts.t1 + not deferrable for each row execute procedure "C_constr"(); + +create constraint trigger "B_constr" after insert on ts.t1 + deferrable initially deferred for each row execute procedure "B_constr"(); + +create trigger "D_trig" after insert on ts.t1 + for each row execute procedure "D_trig"(); + +create trigger "A_trig" after insert on ts.t1 + for each row execute procedure "A_trig"(); + +-- This should run non def constr C_constr. Then A_trig->D_trig. +insert into ts.t1 values(103); +-- On transaction commmit run B_constr +commit; + +drop trigger "B_constr" on ts.t1; +drop trigger "C_constr" on ts.t1; +drop trigger "A_trig" on ts.t1; +drop trigger "D_trig" on ts.t1; + +-- Test 012: TestWhenInternalConstrDeferredThenCheckedAtTransactionCommit +begin; + +-- defers the fkey from t2 to t3 +alter table ts.t3 alter constraint t3_t2_id_fkey deferrable; +set constraints ts.t3_t2_id_fkey deferred; + +create constraint trigger "C_constr" after insert on ts.t3 + not deferrable for each row execute procedure "C_constr"(); + +create constraint trigger "B_constr" after insert on ts.t3 + not deferrable for each row execute procedure "B_constr"(); + +create trigger "D_trig" after insert on ts.t3 + for each row execute procedure "D_trig"(); + +create trigger "A_trig" after insert on ts.t3 + for each row execute procedure "A_trig"(); + +-- Should run all of the other triggers, including the fkey to t1 +-- which it will check first and should pass. +insert into ts.t3 values(21, 10, 1); +-- transaction commit should fail because of fkey constraint to t3 +-- transaction should abort now +commit; + +-- Clean up +drop schema ts cascade; \ No newline at end of file