Technical Analysis: Segmentation Fault from Reentrancy in RI_FKey_cascade_del

Core Problem

This thread identifies a use-after-free vulnerability in PostgreSQL's referential integrity (RI) trigger subsystem that causes a segmentation fault under specific but reproducible conditions. The bug lives in ri_triggers.c, specifically in the RI_FKey_cascade_del function.

Root Cause Chain

1. A table has a self-referential foreign key (FK column references its own PK)
2. The table also has a BEFORE DELETE trigger that performs additional DELETEs on the same table
3. When a row is deleted, the RI cascade trigger fires RI_FKey_cascade_del to propagate the delete to referencing rows
4. During execution of the RI cascade plan (via SPI), the BEFORE DELETE trigger fires on the cascaded rows, which itself deletes more rows from the same table
5. This causes reentrant invocation of RI_FKey_cascade_del
6. During the nested execution, conditions arise that cause ResetPlanCache() to be called (plan invalidation)
7. The plan that the outer invocation of RI_FKey_cascade_del is still executing gets freed
8. When control returns to the outer invocation, it continues using the now-freed plan pointer → segfault in _SPI_execute_plan

Why This Is Architecturally Significant

The RI trigger module maintains a cached plan system (ri_PlanCache) that stores prepared SPI plans for FK enforcement queries. This is a performance optimization — plans are prepared once and reused across trigger firings. However, this caching layer lacks awareness of whether a plan is currently in active execution when deciding whether it's safe to free/invalidate.

This is fundamentally a lifetime management problem: the RI module's plan cache assumes plans have simple create-use-free lifecycles, but reentrancy creates situations where a plan is simultaneously "in use" at multiple call stack depths.

Proposed Solution

The patch introduces reentrancy guards via reference counting on RI plans:

• Each cached plan gains a reference count tracking how many active executions are using it
• Before executing a plan via SPI, the count is incremented
• After execution completes (or on error), the count is decremented
• ResetPlanCache (or the RI module's equivalent plan invalidation) checks the reference count before freeing — if the count is non-zero, the plan is marked for deferred cleanup rather than immediate freeing

This is a well-established pattern (similar to how ResourceOwner tracks buffer pins, or how CachedPlan itself uses refcount in the general plancache). The RI module's private plan cache simply lacked this protection.

Design Considerations and Tradeoffs

Is this a bug or misuse?

Álvaro Herrera raises an important architectural question: should BEFORE DELETE triggers be performing DML (deleting other rows) at all? The PostgreSQL documentation explicitly recommends that:

• BEFORE triggers should only modify row-local state (the tuple being processed)
• AFTER triggers are the appropriate place for side effects on other rows/tables

If this is considered definitionally incorrect usage, the fix might be "don't do that" rather than hardening the RI module against reentrancy. However, PostgreSQL does not prevent this pattern, and a segfault is never an acceptable failure mode regardless of usage correctness.

Implications of the fix

1. Memory lifetime extension: Plans marked "in use" will survive longer than they otherwise would, potentially holding onto slightly more memory in edge cases
2. Correctness of stale plans: If a plan is kept alive past an invalidation event, it may execute with stale catalog assumptions. The fix likely needs to handle this by re-validating or re-preparing the plan after the reference count drops to zero
3. Error path handling: The reference count decrement must be robust against exceptions (likely needs PG_TRY/PG_CATCH or integration with the resource owner system)

Broader Context

This bug class — reentrancy in trigger execution causing lifetime violations — is not unique to RI triggers. The general plancache (plancache.c) handles this via CachedPlan->refcount and CachedPlan->is_valid flags, allowing plans to be invalidated without being freed while pinned. The RI module's private plan cache was essentially reimplementing plan caching without the same safety mechanisms.

The fact that this "survived this long" (as Álvaro notes) suggests the combination of self-referential FKs with row-deleting BEFORE triggers is rare in practice, but it's a legitimate crash bug that should be fixed regardless of whether the usage pattern is recommended.