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...@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(-)
