| From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
|---|---|
| To: | Amit Langote <amitlan(at)postgresql(dot)org> |
| Cc: | pgsql-committers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: pgsql: Add fast path for foreign key constraint checks |
| Date: | 2026-03-31 07:17:31 |
| Message-ID: | CA+HiwqGGYjN6F2oL7yAk=hvSs-sj3TPqZ9JC9iyLkCqJadECrw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-committers |
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
--
Thanks, Amit Langote
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Gustafsson | 2026-03-31 08:01:06 | pgsql: Formalize WAL record for XLOG_CHECKPOINT_REDO |
| Previous Message | Peter Eisentraut | 2026-03-31 06:47:07 | pgsql: meson: Make room for C++-only warning flags for MSVC |