Take RI fast-path snapshot after locking referenced relation ri_FastPathCheck() acquired its scan snapshot before opening the referenced relation. If it then waited for the relation lock in READ COMMITTED mode, a referenced row committed during the wait would not be visible to the old snapshot. The check could consequently report a foreign key violation even though the referenced row existed.
The batched path acquires its scan snapshot after opening the relations, so it does not have the lock-wait problem. The per-row path remains reachable during constraint validation and checks re-entered from a batch flush. Take the snapshot after opening the referenced relation and reloading the constraint information. This also agrees with the SPI path, which acquires the referenced-relation lock before selecting the snapshot used for the check. Also make the scan snapshot active for the duration of the check, so STABLE cast and equality functions use the same snapshot as the index scan. Otherwise, a STABLE cast can miss rows inserted by earlier AFTER triggers and cause a valid foreign key to be rejected. Add a regression test comparing this case with the SPI path. Finally, also advance the command counter after acquiring the referenced-relation lock and before reloading the constraint information, so local invalidations are processed under the lock, as in the SPI path. Add an injection-point test that pauses a per-row check before locking the referenced relation, commits the missing referenced row in another session, and verifies that validation succeeds. Force per-row validation by using a role without SELECT privilege on the referenced table, so the test exercises this path even with batching enabled. Add isolation-test coverage for the visibility of a referenced row committed after the referencing transaction has executed an earlier command. A later command can see such a row in READ COMMITTED, but not in REPEATABLE READ or SERIALIZABLE. Reviewed-by: Chao Li <[email protected]> Reviewed-by: Amit Kapila <[email protected]> Discussion: https://postgr.es/m/CA+HiwqEhm+_=bs=2wavajz-uqc+1kebd31++mapjqqgwee8...@mail.gmail.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/139018268375b3f09995d2c1819b0d838ddacf36 Modified Files -------------- src/backend/utils/adt/ri_triggers.c | 37 +++++++++--- src/test/isolation/expected/fk-snapshot-2.out | 44 ++++++++++++++ src/test/isolation/specs/fk-snapshot-2.spec | 15 +++++ src/test/modules/injection_points/Makefile | 1 + .../expected/ri_fastpath_snapshot.out | 19 ++++++ src/test/modules/injection_points/meson.build | 1 + .../specs/ri_fastpath_snapshot.spec | 50 ++++++++++++++++ src/test/regress/expected/foreign_key.out | 69 ++++++++++++++++++++++ src/test/regress/sql/foreign_key.sql | 65 ++++++++++++++++++++ 9 files changed, 293 insertions(+), 8 deletions(-)
