pgsql: Take RI fast-path snapshot after locking referenced relation

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 <li(dot)evan(dot)chao(at)gmail(dot)com>
Reviewed-by: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Discussion: https://postgr.es/m/CA+HiwqEhm+_=bs=2wavAJz-UqC+1KebD31++mapJQQGweE8iQQ@mail.gmail.com
Backpatch-through: 19

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/3f660d86ba65d9747d5e2da418e24bd023271241

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