MIME-Version: 1.0
References: 
 <CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=Li6KNMqA@mail.gmail.com>
 <CA+HiwqGM6nvAV5O+=Nr+BXMPWOma0oeCRVzVP0XiLE8zX5TVAg@mail.gmail.com>
 <CA+HiwqGMaovCUgDbGxVGnK0Mrivr+ph3YE2Ws+47-ugyPb4f7g@mail.gmail.com>
 <CAFj8pRDaiBe_GOLk_yyYHTtPiDAAaLOM8u1-=Q3ZgXBTH+1igg@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRDaiBe_GOLk_yyYHTtPiDAAaLOM8u1-=Q3ZgXBTH+1igg@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Wed, 22 Oct 2025 22:55:53 +0900
Message-ID: 
 <CA+HiwqGA5Ay_MR0eJEEbt4j6WrVh4F+AasTp8yCbs5aJLOJn6Q@mail.gmail.com>
Subject: Re: Eliminating SPI / SQL from some RI triggers - take 3
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Junwang Zhao <zhjwpku@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BHiwqGA5Ay_MR0eJEEbt4j6WrVh4F%2BAasTp8yCbs5aJLOJn6Q%40mail.gmail.com>
Precedence: bulk

.
On Tue, Oct 21, 2025 at 2:10=E2=80=AFPM Pavel Stehule <pavel.stehule@gmail.=
com> wrote:
> =C3=BAt 21. 10. 2025 v 6:07 odes=C3=ADlatel Amit Langote <amitlangote09@g=
mail.com> napsal:
>>
>> On Thu, Apr 3, 2025 at 7:19=E2=80=AFPM Amit Langote <amitlangote09@gmail=
.com> wrote:
>> > On Fri, Dec 20, 2024 at 1:23=E2=80=AFPM Amit Langote <amitlangote09@gm=
ail.com> wrote:
>> > > We discussed $subject at [1] and [2] and I'd like to continue that
>> > > work with the hope to commit some part of it for v18.
>> >
>> > I did not get a chance to do any further work on this in this cycle,
>> > but plan to start working on it after beta release, so moving this to
>> > the next CF.  I will post a rebased patch after the freeze to keep the
>> > bots green for now.
>>
>> Sorry for the inactivity. I've moved the patch entry in the CF app to
>> PG19-Drafts, since I don't plan to work on it myself in the immediate
>> future. However, Junwang Zhao has expressed interest in taking this
>> work forward, and I look forward to working with him on it.
>
>
> This is very interesting and important feature - I can help with testing =
and review if it will be necessary

Thanks for the interest.

Just to add a quick note on the current direction I=E2=80=99ve been discuss=
ing
off-list with Junwang:

The next iteration of this work will likely follow a hybrid "fast-path
+ fallback" design rather than the original pure fast-path approach.
The idea is to keep the optimization for straightforward cases where
the foreign key and referenced key can be verified by a direct index
probe, while falling back to the existing SPI path only when the
runtime behavior of the executor is non-trivial to replicate -- such
as visibility rechecks under concurrent updates -- or when the
constraint itself involves richer semantics, like temporal foreign
keys that require range and aggregation logic. That keeps the
optimization safe without changing the meaning of constraint
enforcement.

This direction comes partly in response to the feedback from Robert
and Tom in the earlier Eliminating SPI threads, who raised concerns
that a fast path might silently diverge from what the executor does at
runtime in subtle cases. The fallback design aims to address that
directly: it keeps the optimization where it=E2=80=99s clearly safe, but
defers to the existing SPI-based implementation whenever correctness
might depend on executor behavior that would otherwise be difficult or
risky to reproduce locally.

In practice, this means adding a guarded fast path that performs the
index probe and tuple lock directly under the same snapshot and
security context that SPI would use, while caching stable metadata
such as index descriptors, scan keys, and operator information per
constraint or per statement. The fallback to SPI remains for the few
cases that either depend on executor behavior or need features beyond
a simple index probe:

* Concurrent updates or deletes: If table_tuple_lock() reports that
the target tuple was updated or deleted, we delegate to the SPI path
so that EvalPlanQual and visibility rules are applied as today.

* Partitioned parents: Skipped in v1 for simplicity, since they
require routing the probe through the correct partition using
PartitionDirectory. This can be added later as a separate patch once
the core mechanism is stable.

* Temporal foreign keys: These use range overlap and containment
semantics (&&, <@, range_agg()) that inherently involve aggregation
and multiple-row reasoning, so they stay on the SPI path.

Everything else -- multi-column keys, cross-type equality supported by
the index opfamily, collation matching, and RLS/ACL enforcement --
will be handled directly in the fast path.  The security behavior will
mirror the existing SPI path by temporarily switching to the parent
table's owner with SECURITY_LOCAL_USERID_CHANGE | SECURITY_NOFORCE_RLS
around the probe, like ri_PerformCheck() does.

For concurrency, the fast path locks the located parent tuple with
LockTupleKeyShare under GetActiveSnapshot(). If that succeeds (TM_Ok),
the check passes immediately. While non-TM_Ok cases fall back for now,
a later refinement could follow the update chain with
table_tuple_fetch_row_version() under the current snapshot and re-lock
the visible version, making the fast path fully self-contained.

That=E2=80=99s the direction Junwang and I plan to explore next.

--
Thanks, Amit Langote