pgsql: Lock RI fast-path rows as of the scan snapshot's command ID

Поиск
Список
Период
Сортировка
Искать

pgsql: Lock RI fast-path rows as of the scan snapshot's command ID

От:
Amit Langote <amitlan@postgresql.org>
Дата:
Lock RI fast-path rows as of the scan snapshot's command ID

ri_LockPKTuple() passed table_tuple_lock() the current command ID, read
at lock time.  ExecLockRows(), which locks the row for the SPI path's
SELECT ... FOR KEY SHARE, uses the estate's es_output_cid, fixed when
the query starts and equal to the command ID of the query's snapshot.
The two differ if user code run between taking the snapshot and locking
the row, such as the equality function called by the index scan,
advances the command counter.  If that code also updated the referenced
row, the lock reports TM_Invisible, and the check fails with "attempted
to lock invisible tuple", where the SPI path gets TM_SelfModified and
reports a foreign key violation.

To fix, use the snapshot's command ID in ri_LockPKTuple() to mirror
what ExecLockRows() does.

This is mostly hardening.  With FK values that need a cast now checked
through SPI, the only user code that can run there is the opclass's
equality function, and it would have to write to the referenced table.
That's unlikely, but the fast path should still behave like SPI.

Discussion: https://postgr.es/m/CA+HiwqG79XK1oObdZ2AwT660CeJ6s3Mn4LrFPCme-k4L2rF_ag@mail.gmail.com
Backpatch-through: 19

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/061065e28f086e3e2d7191a7785b9f218b7976d8

Modified Files
--------------
src/backend/utils/adt/ri_triggers.c       | 10 +++++++++-
src/test/regress/expected/foreign_key.out | 30 ++++++++++++++++++++++++++++++
src/test/regress/sql/foreign_key.sql      | 29 +++++++++++++++++++++++++++++
3 files changed, 68 insertions(+), 1 deletion(-)

Re: pgsql: Lock RI fast-path rows as of the scan snapshot's command ID

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Thu, Oct 8, 2026 at 12:50 PM Amit Langote  wrote:
> This is mostly hardening.  With FK values that need a cast now checked
> through SPI, the only user code that can run there is the opclass's
> equality function, and it would have to write to the referenced table.
> That's unlikely, but the fast path should still behave like SPI.

Oops, the "FK values that need a cast now checked through SPI" part is
not true as of now because I had assumed when writing it that I'd
commit the patch at [1] to make it true before this one but that
didn't happen.  The fix itself isn't invalidated (it still accounts
for user actions in cast functions), but the description is
unfortunately incorrect at this moment.

-- 
Thanks, Amit Langote

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


FAQ