On Tue, Mar 31, 2026 at 4:17 PM Amit Langote <[email protected]> wrote: > 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
I've pushed a fix: 68a8601ee9ec. -- Thanks, Amit Langote
