Hi Tomas, Thanks for your review. I attached a v6, rebased on current master and with some changes handling some items in your review.
1) Agreed on the naming, there were three different terms for the same thing. I used "free" everywhere, but since the plan is not always freed immediately, the names now say that explicitly: ri_PreparedPlanReleaseASAP() -> ri_PreparedPlanFreeOrDefer() markedForDeletion -> freeDeferred ri_PreparedPlanFreeUnused() -> ri_PreparedPlanFreeDeferred() The comments and the commit message were updated to use the same terms. The only remaining "Release" is ResOwnerReleaseRIPlanExecution(), which follows the naming convention of the other resource owner callbacks (ResOwnerReleaseBuffer(), ResOwnerReleaseCachedPlan(), etc.), so I kept it. 2) Agreed, the "free later list" was not necessary, and I removed it. To be precise, those plans are no longer in ri_query_cache, since ri_FetchPreparedPlan() removes them from it, but their entries remain in the refcount hash table, ri_query_plan_cache_executing_refcount, with a zero count. ri_PreparedPlanFreeDeferred() now scans that table instead. That table only has entries while RI queries are being executed, so it stays small. However, ri_PreparedPlanFreeDeferred() is called on every ri_PerformCheck(), so it returns immediately when the table is empty, which is the case unless an RI query is already being executed or a deferred plan is pending. This way the common path does not pay for a hash_seq_search() over all the buckets. I also reduced the initial size of the table to 16, instead of RI_INIT_QUERYHASHSIZE (256). 3) Yes, all supported branches are affected. However, this fix cannot be backpatched as is: the ResourceOwnerDesc API exists only since 17, and AtEOXact_RI() is new in 19. I would propose to agree on the fix for master first, and then submit a separate, less invasive patch for 14-18, using only the APIs and functions available in those branches. Regards, Lucas Jeffrey El mar, 6 oct 2026 a las 15:26, Tomas Vondra (<[email protected]>) escribió: > Hi, > > On 10/5/26 16:31, Lucas Jeffrey wrote: > > Hi hackers, > > I'll attach a new v5 version of my patch, I rebased so my patches can > > now apply cleanly in master. > > Reviews are welcomed. > > Regards, > > Lucas Jeffrey > > > > I accidentally ran into this issue a couple days ago, unaware of this > thread. I really like the approach of the proposed fix, refcounting > seems like a sound approach and we already useit elsewhere to track > similar objects. I've only done a quick revie, but it seems much cleaner > than my PoC fix. > > A couple (very cosmetic) review comments: > > > 1) The naming seems a bit inconsistent > > static void ri_PreparedPlanReleaseASAP(SPIPlanPtr plan); > static void ri_PreparedPlanFreeUnused(void); > > Why release vs. free? And the flag is called markedForDeletion, so > that's a third term. Also, I wouldn't use the ASAP in function name. > > The SPI function is called SPI_freeplan, so what about using "free" > everywhere? > > static void ri_PreparedPlanFree(SPIPlanPtr plan); > static void ri_PreparedPlanFreeUnused(void); > > Although, I now realize we're not necessarily freeing the plan, so maybe > this is not an improvement ... > > > 2) Is ri_plans_to_free necessary? > > IIUC those plans are still in the hashtable, right? Why not to simply > scan the hashtable, and free the plans there? That'd mean we don't need > a separate list that can get out of sync. Maybe we expect the hash table > to be large, in which case this could be expensive. But is it? I don't > see any such argument in this thread. > > If the list is necessary, maybe ri_PreparedPlanFreeUnused() should at > least have an assert that the hash_search() found a matching entry? > > > 3) What about backpatching? > > AFAIK this affects all backbranches, not just master, right? So we > should aim to fix the backbranches too. Do you think this refcounting > fix is backpatchable (it seems like it'd be OK), or do you have an idea > for a less invasive fix? > > > regards > > -- > Tomas Vondra > >
From b0eb4f7bfac085ac9cfd2513b04a3cb493a98a5d Mon Sep 17 00:00:00 2001 From: Lucas Jeffrey <[email protected]> Date: Mon, 5 Oct 2026 09:27:04 -0300 Subject: [PATCH v6 2/2] Fix use-after-free of RI query plans in reentrant RI checks RI checks can be reentered while running: for example, with an ON DELETE CASCADE foreign key on a self-referencing table and a BEFORE DELETE trigger that deletes from the same table, the cascade's DELETE fires the trigger, whose own DELETE fires the cascade again, which runs the same cached plan. If that plan has been invalidated in the meantime, the nested call's ri_FetchPreparedPlan() sees it as invalid and frees it with SPI_freeplan(), while the outer call is still executing it. The outer call then crashes in _SPI_execute_plan(). To fix, add a backend-local hash table, ri_query_plan_cache_executing_refcount, keyed by the plan pointer, that counts the executions in progress of each RI plan. ri_PerformCheck() creates the plan's entry when it starts executing a plan that has none, or increments its count otherwise, and decrements the count when the execution finishes. When ri_FetchPreparedPlan() finds an invalid plan that has an entry, it removes it from the query hash table as before, but defers freeing it. When the last execution of a plan finishes: - If freeing the plan was not deferred, its entry is removed, and the plan stays in the query hash table as usual. - If freeing it was deferred and the execution finished normally, the entry is removed and the plan is freed right away. - If freeing it was deferred and the execution was aborted by an error, the plan is not freed while the aborted (sub)transaction's resources are being released. Its entry is kept with a zero count, and the plan is freed by the next RI query execution, or at the latest by AtEOXact_RI(), both of which scan the table for such entries. The plan is no longer in the query hash table, so nothing can be using it. To decrement the count when the query throws an error, each execution is registered with the current resource owner, using a new ResourceOwnerDesc, rather than wrapping it in PG_TRY(). When a (sub)transaction aborts, its resource owner decrements the count of the executions that were running in it. If the error is caught by a PL/pgSQL exception block in a trigger, only the executions inside that block's subtransaction are released, and those in the outer levels keep their count. On the normal path this only adds and removes an element of the resource owner's array. ri_query_plan_cache_executing_refcount only has entries while RI queries are being executed, so it is small, and it is empty outside of nested RI checks, in which case the scan done by each RI query execution is skipped. Signed-off-by: Lucas Jeffrey <[email protected]> --- src/backend/utils/adt/ri_triggers.c | 244 +++++++++++++++++++++++++++- src/tools/pgindent/typedefs.list | 1 + 2 files changed, 243 insertions(+), 2 deletions(-) diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index c3ea7db72ff..4b986e57825 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -58,6 +58,7 @@ #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" +#include "utils/resowner.h" #include "utils/rls.h" #include "utils/ruleutils.h" #include "utils/snapmgr.h" @@ -71,6 +72,7 @@ #define RI_INIT_CONSTRAINTHASHSIZE 64 #define RI_INIT_QUERYHASHSIZE (RI_INIT_CONSTRAINTHASHSIZE * 4) +#define RI_INIT_PLANEXECHASHSIZE 16 #define RI_KEYS_ALL_NULL 0 #define RI_KEYS_SOME_NULL 1 @@ -231,12 +233,39 @@ typedef struct RI_CompareHashEntry FmgrInfo cast_func_finfo; /* in case we must coerce input */ } RI_CompareHashEntry; +/* + * RI_QueryPlanCacheExecutingRefCountEntry + * + * Tracks a saved RI plan while ri_PerformCheck() is executing it. RI checks + * can nest: a trigger fired by an RI query may run another RI query using the + * same plan (e.g. ON DELETE CASCADE on a self-referencing table, with a BEFORE + * DELETE trigger that deletes from that table). If the inner call finds the + * plan invalid, it must not free it while an outer call is still executing + * it; instead it defers freeing it, and the last execution frees it. + * + * An entry exists only while the plan is being executed, plus, for a plan + * whose freeing was deferred and whose last execution was aborted by an + * error, until the next RI query execution or the end of the transaction. A plan is never + * freed while it has an entry, so its address cannot be reused by another + * plan in the meantime. + * + * Each execution is also registered with the current resource owner, so that + * the count is decremented even if the query throws an error. + */ +typedef struct RI_QueryPlanCacheExecutingRefCountEntry +{ + SPIPlanPtr plan; /* hash key */ + bool freeDeferred; /* free when refcount reaches 0 */ + uint32 refcount; /* # of executions in progress */ +} RI_QueryPlanCacheExecutingRefCountEntry; + /* * Local data */ static HTAB *ri_constraint_cache = NULL; static HTAB *ri_query_cache = NULL; static HTAB *ri_compare_cache = NULL; +static HTAB *ri_query_plan_cache_executing_refcount = NULL; static dclist_head ri_constraint_cache_valid_list; /* @@ -280,6 +309,22 @@ static SPIPlanPtr ri_FetchPreparedPlan(RI_QueryKey *key); static void ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan); static RI_CompareHashEntry *ri_HashCompareOp(Oid eq_opr, Oid typeid); +static void ri_PreparedPlanExecutionStarted(SPIPlanPtr plan); +static void ri_PreparedPlanExecutionFinished(SPIPlanPtr plan); +static void ri_PreparedPlanDecrementRefCount(SPIPlanPtr plan, bool canFree); +static void ri_PreparedPlanFreeOrDefer(SPIPlanPtr plan); +static void ri_PreparedPlanFreeDeferred(void); +static void ResOwnerReleaseRIPlanExecution(Datum res); + +static const ResourceOwnerDesc ri_plan_execution_resowner_desc = +{ + .name = "RI plan execution", + .release_phase = RESOURCE_RELEASE_AFTER_LOCKS, + .release_priority = RELEASE_PRIO_FIRST, + .ReleaseResource = ResOwnerReleaseRIPlanExecution, + .DebugPrint = NULL /* the default message is fine */ +}; + static void ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname, int tgkind); static RI_ConstraintInfo *ri_FetchConstraintInfo(Trigger *trigger, @@ -2726,11 +2771,17 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, * Set fire_triggers to false to ensure that AFTER triggers are queued in * the outer query's after-trigger context and fire after all RI updates * on the same row are complete, rather than immediately. + * + * Triggers fired by the query may reach ri_FetchPreparedPlan() for the + * same query key and find the plan invalid, so tell it that the plan is + * in use while it runs. */ + ri_PreparedPlanExecutionStarted(qplan); spi_result = SPI_execute_snapshot(qplan, vals, nulls, test_snapshot, crosscheck_snapshot, false, false, limit); + ri_PreparedPlanExecutionFinished(qplan); /* Restore UID and security context */ SetUserIdAndSecContext(save_userid, save_sec_context); @@ -3652,6 +3703,13 @@ ri_InitHashTables(void) ri_compare_cache = hash_create("RI compare cache", RI_INIT_QUERYHASHSIZE, &ctl, HASH_ELEM | HASH_BLOBS); + + ctl.keysize = sizeof(SPIPlanPtr); + ctl.entrysize = sizeof(RI_QueryPlanCacheExecutingRefCountEntry); + ri_query_plan_cache_executing_refcount = + hash_create("RI plan execution refcount", + RI_INIT_PLANEXECHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); } @@ -3698,11 +3756,13 @@ ri_FetchPreparedPlan(RI_QueryKey *key) /* * Otherwise we might as well flush the cached plan now, to free a little - * memory space before we make a new one. + * memory space before we make a new one. An RI check further up the + * stack may still be executing it, though, in which case it's freed only + * once that execution finishes. */ entry->plan = NULL; if (plan) - SPI_freeplan(plan); + ri_PreparedPlanFreeOrDefer(plan); return NULL; } @@ -3737,6 +3797,174 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) } +/* + * ri_PreparedPlanExecutionStarted - + * + * Record that ri_PerformCheck() is about to execute a saved plan, so that it + * is not freed under us. The execution is also registered with the current + * resource owner, which undoes this if the execution is aborted by an error. + * + * This is also where plans left over by aborted executions are freed, if any. + * The plan about to be executed cannot be one of them: those are no longer in + * the query hashtable, and their addresses cannot have been reused yet. + */ +static void +ri_PreparedPlanExecutionStarted(SPIPlanPtr plan) +{ + RI_QueryPlanCacheExecutingRefCountEntry *entry; + bool found; + + /* The plan came from ri_FetchPreparedPlan() or ri_PlanCheck() */ + Assert(ri_query_plan_cache_executing_refcount != NULL); + + ri_PreparedPlanFreeDeferred(); + + ResourceOwnerEnlarge(CurrentResourceOwner); + + entry = (RI_QueryPlanCacheExecutingRefCountEntry *) + hash_search(ri_query_plan_cache_executing_refcount, + &plan, HASH_ENTER, &found); + if (!found) + { + entry->freeDeferred = false; + entry->refcount = 0; + } + entry->refcount++; + + ResourceOwnerRemember(CurrentResourceOwner, PointerGetDatum(plan), + &ri_plan_execution_resowner_desc); +} + +/* + * ri_PreparedPlanExecutionFinished - + * + * Record that an execution started by ri_PreparedPlanExecutionStarted() has + * finished normally. If it was the last execution of a plan whose freeing + * was deferred in the meantime, the plan is freed. + */ +static void +ri_PreparedPlanExecutionFinished(SPIPlanPtr plan) +{ + ResourceOwnerForget(CurrentResourceOwner, PointerGetDatum(plan), + &ri_plan_execution_resowner_desc); + ri_PreparedPlanDecrementRefCount(plan, true); +} + +/* + * ResOwnerReleaseRIPlanExecution - + * + * ResourceOwner callback, for an execution aborted by an error. We only drop + * the count here: a plan whose freeing was deferred and whose last execution + * this was is freed later by ri_PreparedPlanFreeDeferred(), called by the + * next RI query execution or at the end of the transaction, rather than while + * releasing the aborted (sub)transaction's resources. + */ +static void +ResOwnerReleaseRIPlanExecution(Datum res) +{ + ri_PreparedPlanDecrementRefCount((SPIPlanPtr) DatumGetPointer(res), false); +} + +/* + * ri_PreparedPlanDecrementRefCount - + * + * Drop one execution of a plan, removing its entry once no execution is left. + * If freeing the plan was deferred, it is freed at that point if canFree; + * otherwise its entry is left in place, with a zero count, for + * ri_PreparedPlanFreeDeferred() to find. + */ +static void +ri_PreparedPlanDecrementRefCount(SPIPlanPtr plan, bool canFree) +{ + RI_QueryPlanCacheExecutingRefCountEntry *entry; + bool freeDeferred; + + entry = (RI_QueryPlanCacheExecutingRefCountEntry *) + hash_search(ri_query_plan_cache_executing_refcount, + &plan, HASH_FIND, NULL); + Assert(entry != NULL); + Assert(entry->refcount > 0); + + entry->refcount--; + if (entry->refcount > 0) + return; + + freeDeferred = entry->freeDeferred; + if (freeDeferred && !canFree) + return; + + hash_search(ri_query_plan_cache_executing_refcount, + &plan, HASH_REMOVE, NULL); + if (freeDeferred) + SPI_freeplan(plan); +} + +/* + * ri_PreparedPlanFreeOrDefer - + * + * Free a saved plan that has just been removed from the query hashtable, or, + * if it is being executed, defer freeing it until the last execution + * finishes. + */ +static void +ri_PreparedPlanFreeOrDefer(SPIPlanPtr plan) +{ + RI_QueryPlanCacheExecutingRefCountEntry *entry; + + /* Called from ri_FetchPreparedPlan(), so the hashtables exist */ + Assert(ri_query_plan_cache_executing_refcount != NULL); + + entry = (RI_QueryPlanCacheExecutingRefCountEntry *) + hash_search(ri_query_plan_cache_executing_refcount, + &plan, HASH_FIND, NULL); + if (entry == NULL) + { + SPI_freeplan(plan); + return; + } + + Assert(entry->refcount > 0); + Assert(!entry->freeDeferred); + entry->freeDeferred = true; +} + +/* + * ri_PreparedPlanFreeDeferred - + * + * Free the plans whose freeing was deferred and whose last execution was + * aborted by an error, which ri_PreparedPlanDecrementRefCount() left in the + * hash table with a zero count. + * + * This is called for every RI query execution, but the hash table only has + * entries while RI queries are being executed, so outside of nested RI checks + * it is empty and there is nothing to scan. + */ +static void +ri_PreparedPlanFreeDeferred(void) +{ + HASH_SEQ_STATUS status; + RI_QueryPlanCacheExecutingRefCountEntry *entry; + + if (ri_query_plan_cache_executing_refcount == NULL || + hash_get_num_entries(ri_query_plan_cache_executing_refcount) == 0) + return; + + hash_seq_init(&status, ri_query_plan_cache_executing_refcount); + while ((entry = hash_seq_search(&status)) != NULL) + { + SPIPlanPtr plan = entry->plan; + + if (entry->refcount > 0) + continue; + Assert(entry->freeDeferred); + + if (hash_search(ri_query_plan_cache_executing_refcount, + &plan, HASH_REMOVE, NULL) == NULL) + elog(ERROR, "hash table corrupted"); + SPI_freeplan(plan); + } +} + /* * ri_KeysEqual - * @@ -4023,6 +4251,9 @@ RI_FKey_trigger_type(Oid tgfoid) * other AtEOXact_* routines but is not used: the release is the same on * the commit and the abort path. * + * It also frees the RI plans whose freeing was deferred and that are still + * pending; see RI_QueryPlanCacheExecutingRefCountEntry. + * * There is no AtEOSubXact_RI() counterpart. Nothing here is scoped to a * subtransaction: a detached FastPathMeta stays reachable from the dead * list whichever subtransaction detached it, and a check holding a pointer @@ -4040,4 +4271,13 @@ AtEOXact_RI(bool isCommit) MemoryContextDelete(dead->scratch_cxt); pfree(dead); } + + /* + * Likewise, free plans whose freeing was deferred and whose last + * execution was aborted. All subtransactions and resource owners have + * been released by now, so the hash table must end up empty. + */ + ri_PreparedPlanFreeDeferred(); + Assert(ri_query_plan_cache_executing_refcount == NULL || + hash_get_num_entries(ri_query_plan_cache_executing_refcount) == 0); } diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index d001f56efae..bea4c4d5848 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2502,6 +2502,7 @@ RI_ConstraintInfo RI_FastPathState RI_QueryHashEntry RI_QueryKey +RI_QueryPlanCacheExecutingRefCountEntry RTEKind RTEPermissionInfo RWConflict -- 2.34.1
From 0369a9de78a3d6281463ebacea5ed9641dd3cca2 Mon Sep 17 00:00:00 2001 From: Lucas Jeffrey <[email protected]> Date: Thu, 24 Sep 2026 12:56:54 -0300 Subject: [PATCH v6 1/2] Add test for reentrant ON DELETE CASCADE on a self-referencing table Add a regression test for a crash in RI_FKey_cascade_del(): with an ON DELETE CASCADE foreign key on a self-referencing table and a BEFORE DELETE trigger that deletes from that same table, the RI cascade query can be reentered while it's executing. If the cascade's plan gets invalidated meanwhile, the nested call finds it invalid in ri_FetchPreparedPlan() and frees it, while the outer call is still executing it, leading to a use-after-free in _SPI_execute_plan(). The test also checks the rows that survive the cascade, and covers the error path: the nested cascade raises an error, caught by a PL/pgSQL exception block, several times in a row, while the invalidated plans are still being executed; a plain DELETE after a further invalidation must still work (and not trip any assertion). Signed-off-by: Lucas Jeffrey <[email protected]> --- src/test/regress/expected/foreign_key.out | 75 +++++++++++++++++++++++ src/test/regress/sql/foreign_key.sql | 55 +++++++++++++++++ 2 files changed, 130 insertions(+) diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index df5e827d57e..74ca558b58b 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -4180,3 +4180,78 @@ ERROR: insert or update on table "fp_fk_cid" violates foreign key constraint "f DETAIL: Key (a)=(1) is not present in table "fp_pk_cid". ROLLBACK TO SAVEPOINT fp_cid; ROLLBACK; +-- ON DELETE CASCADE on a self-referencing table, with a BEFORE DELETE trigger +-- that deletes from the same table. The cascade's plan is invalidated (by +-- the GRANT) while the cascade is executing it, and the nested DELETE reaches +-- the same RI query again; the outer execution must still be able to finish. +CREATE TABLE fk_self_ref ( + id int PRIMARY KEY, + parent_id int REFERENCES fk_self_ref (id) ON DELETE CASCADE +); +CREATE FUNCTION fk_self_ref_before_del() RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF OLD.id = 2 THEN + GRANT SELECT ON fk_self_ref TO PUBLIC; + REVOKE SELECT ON fk_self_ref FROM PUBLIC; + DELETE FROM fk_self_ref WHERE parent_id = OLD.id; + ELSIF OLD.id = 4 AND current_setting('fk_self_ref.fail', true) = 'on' THEN + RAISE EXCEPTION 'fk_self_ref: deleting %', OLD.id; + END IF; + RETURN OLD; +END$$; +CREATE TRIGGER fk_self_ref_before_del BEFORE DELETE ON fk_self_ref + FOR EACH ROW EXECUTE PROCEDURE fk_self_ref_before_del(); +INSERT INTO fk_self_ref VALUES (1, NULL), (2, 1), (3, 2), (4, 3), (5, 4), + (10, NULL), (11, 10); +DELETE FROM fk_self_ref WHERE id = 1; +SELECT * FROM fk_self_ref ORDER BY id; + id | parent_id +----+----------- + 10 | + 11 | 10 +(2 rows) + +-- Same, but with the nested cascade failing, which aborts the executions +-- that are using the plans. Their pins must be released on error, so that +-- the plans can be replaced and freed afterwards. +INSERT INTO fk_self_ref VALUES (1, NULL), (2, 1), (3, 2), (4, 3), (5, 4); +SET fk_self_ref.fail = on; +DO $$ +DECLARE + failures int := 0; +BEGIN + FOR i IN 1..10 LOOP + BEGIN + DELETE FROM fk_self_ref WHERE id = 1; + EXCEPTION WHEN raise_exception THEN + failures := failures + 1; + END; + END LOOP; + RAISE NOTICE 'failures: %', failures; +END$$; +NOTICE: failures: 10 +SELECT * FROM fk_self_ref ORDER BY id; + id | parent_id +----+----------- + 1 | + 2 | 1 + 3 | 2 + 4 | 3 + 5 | 4 + 10 | + 11 | 10 +(7 rows) + +RESET fk_self_ref.fail; +GRANT SELECT ON fk_self_ref TO PUBLIC; +REVOKE SELECT ON fk_self_ref FROM PUBLIC; +DELETE FROM fk_self_ref WHERE id = 1; +SELECT * FROM fk_self_ref ORDER BY id; + id | parent_id +----+----------- + 10 | + 11 | 10 +(2 rows) + +DROP TABLE fk_self_ref; +DROP FUNCTION fk_self_ref_before_del(); diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index ce35c465b81..f2ee4c6a684 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -3115,3 +3115,58 @@ SAVEPOINT fp_cid; INSERT INTO fp_fk_cid VALUES (1); -- fails, as a violation ROLLBACK TO SAVEPOINT fp_cid; ROLLBACK; + +-- ON DELETE CASCADE on a self-referencing table, with a BEFORE DELETE trigger +-- that deletes from the same table. The cascade's plan is invalidated (by +-- the GRANT) while the cascade is executing it, and the nested DELETE reaches +-- the same RI query again; the outer execution must still be able to finish. +CREATE TABLE fk_self_ref ( + id int PRIMARY KEY, + parent_id int REFERENCES fk_self_ref (id) ON DELETE CASCADE +); +CREATE FUNCTION fk_self_ref_before_del() RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF OLD.id = 2 THEN + GRANT SELECT ON fk_self_ref TO PUBLIC; + REVOKE SELECT ON fk_self_ref FROM PUBLIC; + DELETE FROM fk_self_ref WHERE parent_id = OLD.id; + ELSIF OLD.id = 4 AND current_setting('fk_self_ref.fail', true) = 'on' THEN + RAISE EXCEPTION 'fk_self_ref: deleting %', OLD.id; + END IF; + RETURN OLD; +END$$; +CREATE TRIGGER fk_self_ref_before_del BEFORE DELETE ON fk_self_ref + FOR EACH ROW EXECUTE PROCEDURE fk_self_ref_before_del(); + +INSERT INTO fk_self_ref VALUES (1, NULL), (2, 1), (3, 2), (4, 3), (5, 4), + (10, NULL), (11, 10); +DELETE FROM fk_self_ref WHERE id = 1; +SELECT * FROM fk_self_ref ORDER BY id; + +-- Same, but with the nested cascade failing, which aborts the executions +-- that are using the plans. Their pins must be released on error, so that +-- the plans can be replaced and freed afterwards. +INSERT INTO fk_self_ref VALUES (1, NULL), (2, 1), (3, 2), (4, 3), (5, 4); +SET fk_self_ref.fail = on; +DO $$ +DECLARE + failures int := 0; +BEGIN + FOR i IN 1..10 LOOP + BEGIN + DELETE FROM fk_self_ref WHERE id = 1; + EXCEPTION WHEN raise_exception THEN + failures := failures + 1; + END; + END LOOP; + RAISE NOTICE 'failures: %', failures; +END$$; +SELECT * FROM fk_self_ref ORDER BY id; +RESET fk_self_ref.fail; +GRANT SELECT ON fk_self_ref TO PUBLIC; +REVOKE SELECT ON fk_self_ref FROM PUBLIC; +DELETE FROM fk_self_ref WHERE id = 1; +SELECT * FROM fk_self_ref ORDER BY id; + +DROP TABLE fk_self_ref; +DROP FUNCTION fk_self_ref_before_del(); base-commit: c652cd3f94d57e69965f065c4ed45708a2b2a757 -- 2.34.1
