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

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.

--
Thanks, Amit Langote