Re: pgsql: Add fast path for foreign key constraint checks

On Tue, Mar 31, 2026 at 5:08 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Tue, Mar 31, 2026 at 4:17 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > On Tue, Mar 31, 2026 at 1:54 PM Amit Langote <amitlan(at)postgresql(dot)org> wrote:
> > > Add fast path for foreign key constraint checks
> > >
> > > Add a fast-path optimization for foreign key checks that bypasses SPI
> > > by directly probing the unique index on the referenced table.
> > > Benchmarking shows ~1.8x speedup for bulk FK inserts (int PK/int FK,
> > > 1M rows, where PK table and index are cached).
> > >
> > > The fast path applies when the referenced table is not partitioned and
> > > the constraint does not involve temporal semantics. Otherwise, the
> > > existing SPI path is used.
> > >
> > > This optimization covers only the referential check trigger
> > > (RI_FKey_check). The action triggers (CASCADE, SET NULL, SET DEFAULT,
> > > RESTRICT, NO ACTION) must find rows on the FK side to modify, which
> > > requires a table scan with no guaranteed index available, and then
> > > execute DML against those rows through the full executor path including
> > > any triggered actions. Replicating that without substantial code
> > > duplication is not feasible, so those triggers remain on the SPI path.
> > > Extending the fast path to action triggers remains possible as future
> > > work if the necessary infrastructure is built.
> > >
> > > The new ri_FastPathCheck() function extracts the FK values, builds scan
> > > keys, performs an index scan, and locks the matching tuple with
> > > LockTupleKeyShare via ri_LockPKTuple(), which handles the RI-specific
> > > subset of table_tuple_lock() results.
> > >
> > > If the locked tuple was reached by chasing an update chain
> > > (tmfd.traversed), recheck_matched_pk_tuple() verifies that the key
> > > is still the same, emulating EvalPlanQual.
> > >
> > > The scan uses GetTransactionSnapshot(), matching what the SPI path
> > > uses (via _SPI_execute_plan pushing GetTransactionSnapshot() as the
> > > active snapshot). Under READ COMMITTED this is a fresh snapshot;
> > > under REPEATABLE READ / SERIALIZABLE it is the frozen transaction-
> > > start snapshot, so PK rows committed after the transaction started
> > > are not visible.
> > >
> > > The ri_CheckPermissions() function performs schema USAGE and table
> > > SELECT checks, matching what the SPI path gets implicitly through
> > > the executor's permission checks. The fast path also switches to
> > > the PK table owner's security context (with SECURITY_NOFORCE_RLS)
> > > before the index probe, matching the SPI path where the query runs
> > > as the table owner.
> > >
> > > ri_HashCompareOp() is adjusted to handle cross-type equality operators
> > > (e.g. int48eq for int4 PK / int8 FK) which can appear in conpfeqop.
> > > The existing code asserted same-type operators only, which was correct
> > > for its existing callers (ri_KeysEqual compares same-type FK column
> > > values via ff_eq_oprs), but the fast path is the first caller to pass
> > > pf_eq_oprs, which can be cross-type.
> > >
> > > Per-key metadata (compare entries, operator procedures, strategy
> > > numbers) is cached in RI_ConstraintInfo via
> > > ri_populate_fastpath_metadata() on first use, eliminating repeated
> > > calls to ri_HashCompareOp() and get_op_opfamily_properties().
> > > conindid and pk_is_partitioned are also cached at constraint load
> > > time, avoiding per-invocation syscache lookups and the need to open
> > > pk_rel before deciding whether the fast path applies.
> > >
> > > New regression tests cover RLS bypass and ACL enforcement for the
> > > fast-path permission checks. New isolation tests exercise concurrent
> > > PK updates under both READ COMMITTED and REPEATABLE READ.
> > >
> > > Author: Junwang Zhao <zhjwpku(at)gmail(dot)com>
> > > Co-authored-by: Amit Langote <amitlangote09(at)gmail(dot)com>
> > > Reviewed-by: Haibo Yan <tristan(dot)yim(at)gmail(dot)com>
> > > Tested-by: Tomas Vondra <tomas(at)vondra(dot)me>
> > > Discussion: https://postgr.es/m/CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=Li6KNMqA@mail.gmail.com
> > >
> > > Branch
> > > ------
> > > master
> > >
> > > Details
> > > -------
> > > https://git.postgresql.org/pg/commitdiff/2da86c1ef9b5446e0e22c0b6a5846293e58d98e3
> > >
> > > Modified Files
> > > --------------
> > > src/backend/utils/adt/ri_triggers.c | 466 ++++++++++++++++++++-
> > > .../isolation/expected/fk-concurrent-pk-upd.out | 105 +++++
> > > src/test/isolation/isolation_schedule | 1 +
> > > src/test/isolation/specs/fk-concurrent-pk-upd.spec | 53 +++
> > > src/test/regress/expected/foreign_key.out | 47 +++
> > > src/test/regress/sql/foreign_key.sql | 64 +++
> > > src/tools/pgindent/typedefs.list | 1 +
> > > 7 files changed, 723 insertions(+), 14 deletions(-)
> >
> > I'm looking at the failures on prion:
> > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2026-03-31%2006%3A53%3A05
> >
> > They all look like this:
> > +ERROR: could not open relation with OID 2139062143
>
> I've pushed a fix: 68a8601ee9ec.

Found additional issues when testing locally with
CLOBBER_CACHE_ALWAYS: a dangling fpmeta pointer after constraint cache
invalidation, and riinfo going stale inside ri_FastPathCheck() after
relation opens. The attached patch fixes both. I'll apply it tomorrow
morning barring objections.

--
Thanks, Amit Langote