Re: Two more RI fast-path issues

On Tue, Oct 6, 2026 at 3:20 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Tue, Oct 6, 2026 at 3:13 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > While reviewing the RI fast path after removing batching from master,
> > with help from Claude Opus 5.5, I found two more places where it
> > behaves differently from the SPI path, apart from the cast issues
> > discussed in [1].
> >
> > 1. Fast-path doesn't refuse to take row locks in a read-only
> > transaction, whereas the SPI path does. In the SPI path, the executor
> > calls PreventCommandIfReadOnly(), but the fast-path continues without
> > checking. With pk as a plain table and ppk as a partitioned one
> > (whose checks use SPI), and both foreign keys deferred:
> >
> > CREATE TABLE pk (a int PRIMARY KEY); INSERT INTO pk VALUES (1);
> > CREATE TABLE ppk (a int PRIMARY KEY) PARTITION BY LIST (a);
> > CREATE TABLE ppk1 PARTITION OF ppk DEFAULT; INSERT INTO ppk VALUES (1);
> > CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY DEFERRED);
> > CREATE TABLE pfk (a int REFERENCES ppk DEFERRABLE INITIALLY DEFERRED);
> >
> > -- SPI
> > BEGIN; INSERT INTO pfk VALUES (1); SET TRANSACTION READ ONLY;
> > COMMIT;
> > ERROR: cannot execute SELECT FOR KEY SHARE in a read-only transaction
> > CONTEXT: SQL statement "SELECT 1 FROM "public"."ppk" x WHERE "a"
> > OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
> >
> > -- fast -path
> > BEGIN; INSERT INTO fk VALUES (1); SET TRANSACTION READ ONLY;
> > COMMIT;
> > COMMIT
> >
> > 2. Fast-path can produce "attempted to lock invisible tuple" error
> > due to a bug in how it calls table_tuple_lock(). In the SPI path the
> > ExecLockRows() calls table_tuple_lock() with es_output_cid, whereas
> > the fast-path uses GetCurrentCommandId(), which can result in the
> > latter case using a command ID that is different from when the initial
> > snapshot was taken. If user code running during the scan advances it,
> > and if that code also updated the referenced row, the fast path can
> > fail with "attempt to lock invisible tuple" when it should report an
> > FK violation (). In this case, user code is the index's equality
> > operator as shown in the test case added in 0002.
> >
> > Attached patches to fix both, which I'd like to get in before RC1 freeze.
>
> I have added an open item for this.

Barring comments, would like to push the attached slightly updated
version (comment, commit message fixes) tomorrow after committing the
patches in [1] because I rebased these over those other patches.

--
Thanks, Amit Langote

[1] https://www.postgresql.org/message-id/CA%2BHiwqGDY_T4NxBi-kg4Y3V%3DD1CmRhUmj_tyo8aP-ZGO145%3DEQ%40mail.gmail.com