On Tue, Mar 31, 2026 at 1:54 PM Amit Langote <[email protected]> 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 <[email protected]> > Co-authored-by: Amit Langote <[email protected]> > Reviewed-by: Haibo Yan <[email protected]> > Tested-by: Tomas Vondra <[email protected]> > Discussion: > https://postgr.es/m/CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=li6kn...@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 -- Thanks, Amit Langote
