Re: Remove inner joins based on foreign keys

On Mon, Mar 23, 2026 at 3:12 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> Just for the sake of archives, the timeline of the trap during a
> cascading delete looks like this:
>
> T0: DELETE FROM users WHERE id = 1;
>
> T1: The executor finds users row 1 and sets its xmax, physically
> marking it as dead.
>
> T2: [The Gap] The executor pushes the RI trigger into a queue to deal
> with orders later. Right now, the orders row still exists, but its
> referenced row in users is dead.
>
> T3: The statement finishes, the trigger fires, and the orders row is
> finally deleted.
>
> The "T2 Gap" is small, but there are several ways to execute user code
> inside that window, such as RETURNING clauses, volatile functions, or
> user-defined AFTER ROW triggers.

I've been exploring ways to detect whether a query is executing within
the RI trigger gap. It seems one promising approach is to leverage
the lock manager.

Every DML statement acquires RowExclusiveLock on its target table
before modifying any rows, so if the trigger gap is active, the lock
is guaranteed to be held. We can verify this during planning by
calling CheckRelationOidLockedByMe(), which is a quite efficient
check. (As a point of precedent, the planner already relies on the
local lock manager's state in several existing code paths.)

For prepared statements and cached plans, a generic plan that was
built with FK join removal could be reused in a context where the
trigger gap is active. To handle this, we can modify
choose_custom_plan() to check whether any relation involved in an
FK-based join removal currently holds RowExclusiveLock, and choose
custom plan if so.

The trade-off is false positives: because RowExclusiveLock persists
for the entire transaction while the trigger gap is intra-statement,
the optimization is also skipped after the DML completes but within
the same transaction, after ROLLBACK TO a savepoint, or when
RowExclusiveLock is held for other reasons (e.g., LOCK TABLE). These
seem like uncommon cases, and the query simply falls back to executing
the join normally as it would without the optimization at all, so I
think this is an acceptable trade-off.

Please see the v2 patch for the implementation details.

I didn't find any mention of this approach in the 2014 thread. I'd
appreciate any thoughts or feedback on this direction.

- Richard