Horton
Menu

Re: Eliminating SPI / SQL from some RI triggers - take 3

amit <amitlangote09@gmail.com>

View thread
From: Amit Langote <amitlangote09@gmail.com>
To: Junwang Zhao <zhjwpku@gmail.com>
Cc: Haibo Yan <tristan.yim@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas@vondra.me>
Date: 2026-03-24T11:47:17Z
Lists: pgsql-hackers

Attachments

Hi Junwang,

On Fri, Mar 20, 2026 at 1:20 AM Junwang Zhao <zhjwpku@gmail.com> wrote:
> I squashed 0004 into 0003 so that each file can be committed independently.
> I also runned pgindent for each file.

Thanks for that.

Here's another version.

In 0001, I noticed that the condition change in ri_HashCompareOp could
be simplified further.  Also improved the commentary surrounding that.
I also updated the commit message to clarify parity with the SPI path.

Updated the commit message of 0002 to talk about why caching the
snapshot for the entire trigger firing cycle of a given constraint
makes a trade off compared to the SPI path which retakes the snapshot
for every row checked and could in principle avoid failure for FK rows
whose corresponding PK row was added by a concurrently committed
transaction, at least in the READ COMMITTED case.

Updated the commit message of 0003 to clarify that it replaces
ri_FastPathCheckCached() from 0002 with the BatchAdd/BatchFlush pair,
and that the cached resources are used unchanged -- only the probing
cadence changes from per-row to per-flush.  Per-flush CCI is safe
because all AFTER triggers for the buffered rows have already fired
by flush time; a new test case is added to show that.

Finally I added a short line at the end of each patch's commit message
to mention the speedup observed at each stage.  There are placeholders
such as <commit-hash-0001> that I will replace by an actual commit
hash before  pushing.

I will continue staring at these for any remaining issues before
pushing them one-by-one at some point by early next week.  Happy to
hear any thoughts before I push.

-- 
Thanks, Amit Langote

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix some const qualifier use in ri_triggers.c

  2. doc: Improve consistency of parallel vacuum description.

  3. Move afterTriggerFiringDepth into AfterTriggersData

  4. Fix typo left by 34a30786293

  5. Fix RI fast-path crash under nested C-level SPI

  6. Add nkeys parameter to recheck_matched_pk_tuple()

  7. Fix deferred FK check batching introduced by commit b7b27eb41a5

  8. Optimize fast-path FK checks with batched index probes

  9. Make FastPathMeta self-contained by copying FmgrInfo structs

  10. Add fast path for foreign key constraint checks