Re: Two more RI fast-path issues - Mailing list pgsql-hackers

On Wed, Oct 7, 2026 at 10:02 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Tue, Oct 6, 2026 at 3:20 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > On Tue, Oct 6, 2026 at 3:13 PM Amit Langote <amitlangote09@gmail.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.

I've pushed these.

--
Thanks, Amit Langote