On Tue, Oct 6, 2026 at 3:20 PM Amit Langote <[email protected]> wrote: > On Tue, Oct 6, 2026 at 3:13 PM Amit Langote <[email protected]> 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
v2-0002-Lock-RI-fast-path-rows-as-of-the-scan-snapshot-s-.patch
Description: Binary data
v2-0001-Refuse-RI-fast-path-row-locks-in-read-only-transa.patch
Description: Binary data
