On Fri, Jul 18, 2025 at 3:29 AM Dean Rasheed <dean.a.rash...@gmail.com> wrote:
> I agree that additional testing in this area is valuable. Patch 0001
> looks reasonable to me, on a quick read-through. In patch 0002, I
> think it would be valuable to also test updating the parent row to
> time periods consistent and not consistent with the insert, to confirm
> that that behaves correctly.
Thanks for taking a look! Here are new patches with those extra tests.
There are extensive regress tests already, so I just tested the same
concurrency pattern. I think the results are okay. I do get a
can't-serialize exception for a couple valid changes under REPEATABLE
READ and SERIALIZE, but I think they are expected and not a bug. (I
think you would see the same thing outside of FKs.)
--
Paul ~{:-)
p...@illuminatedcomputing.com
From 27af6fb012770e06dff7d606a3cf1894cb73730e Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 15 May 2025 10:41:18 -0400
Subject: [PATCH v3 1/3] Fill testing gap for possible referential integrity
violation
This commit adds a missing isolation test for (non-PERIOD) foreign
keys. With REPEATABLE READ, one transaction can insert a referencing
row while another deletes the referenced row, and both see a valid
state. But after they have committed, the table violates referential
integrity.
If the INSERT precedes the DELETE, we use a crosscheck snapshot to see
the just-added row, so that the DELETE can raise a foreign key error.
You can see the table violate referential integrity if you change
ri_restrict to pass false for detectNewRows to ri_PerformCheck.
A crosscheck snapshot is not needed when the DELETE comes first,
because the INSERT's trigger takes a FOR KEY SHARE lock that sees the
row now marked for deletion, waits for that transaction to commit, and
raises a serialization error. I added a test for that too though.
We already have a similar test (in ri-triggers.spec) for SERIALIZABLE
snapshot isolation showing that you can implement foreign keys with
just pl/pgSQL, but that test does nothing to validate ri_triggers.c. We
also have tests (in fk-snapshot.spec) for other concurrency scenarios,
but not this one: we test concurrently deleting both the referencing
and referenced row, when the constraint activates a cascade/set null
action. But those tests don't exercise ri_restrict, and the consequence
of omitting a crosscheck comparison is different: a serialization
failure, not a referential integrity violation.
---
src/test/isolation/expected/fk-snapshot-2.out | 61 +++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
src/test/isolation/specs/fk-snapshot-2.spec | 50 +++++++++++++++
3 files changed, 112 insertions(+)
create mode 100644 src/test/isolation/expected/fk-snapshot-2.out
create mode 100644 src/test/isolation/specs/fk-snapshot-2.spec
diff --git a/src/test/isolation/expected/fk-snapshot-2.out b/src/test/isolation/expected/fk-snapshot-2.out
new file mode 100644
index 00000000000..0a4c9646fca
--- /dev/null
+++ b/src/test/isolation/expected/fk-snapshot-2.out
@@ -0,0 +1,61 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1rr s2rr s2ins s1del s2c s1c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2ins: INSERT INTO child VALUES (1, 1);
+step s1del: DELETE FROM parent WHERE parent_id = 1; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rr s2rr s1del s2ins s1c s2c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1del: DELETE FROM parent WHERE parent_id = 1;
+step s2ins: INSERT INTO child VALUES (1, 1); <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1rc s2rc s2ins s1del s2c s1c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2ins: INSERT INTO child VALUES (1, 1);
+step s1del: DELETE FROM parent WHERE parent_id = 1; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rc s2rc s1del s2ins s1c s2c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s1del: DELETE FROM parent WHERE parent_id = 1;
+step s2ins: INSERT INTO child VALUES (1, 1); <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: insert or update on table "child" violates foreign key constraint "child_parent_id_fkey"
+step s2c: COMMIT;
+
+starting permutation: s1ser s2ser s2ins s1del s2c s1c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ins: INSERT INTO child VALUES (1, 1);
+step s1del: DELETE FROM parent WHERE parent_id = 1; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1ser s2ser s1del s2ins s1c s2c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1del: DELETE FROM parent WHERE parent_id = 1;
+step s2ins: INSERT INTO child VALUES (1, 1); <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index e3c669a29c7..12b6581d5ab 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -35,6 +35,7 @@ test: fk-deadlock2
test: fk-partitioned-1
test: fk-partitioned-2
test: fk-snapshot
+test: fk-snapshot-2
test: subxid-overflow
test: eval-plan-qual
test: eval-plan-qual-trigger
diff --git a/src/test/isolation/specs/fk-snapshot-2.spec b/src/test/isolation/specs/fk-snapshot-2.spec
new file mode 100644
index 00000000000..94cd151aab9
--- /dev/null
+++ b/src/test/isolation/specs/fk-snapshot-2.spec
@@ -0,0 +1,50 @@
+# RI Trigger test
+#
+# Test C-based referential integrity enforcement.
+# Under REPEATABLE READ we need some snapshot trickery in C,
+# or we would permit things that violate referential integrity.
+
+setup
+{
+ CREATE TABLE parent (parent_id SERIAL NOT NULL PRIMARY KEY);
+ CREATE TABLE child (
+ child_id SERIAL NOT NULL PRIMARY KEY,
+ parent_id INTEGER REFERENCES parent);
+ INSERT INTO parent VALUES(1);
+}
+
+teardown { DROP TABLE parent, child; }
+
+session s1
+step s1rc { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s1rr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s1ser { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step s1del { DELETE FROM parent WHERE parent_id = 1; }
+step s1c { COMMIT; }
+
+session s2
+step s2rc { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s2rr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s2ser { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step s2ins { INSERT INTO child VALUES (1, 1); }
+step s2c { COMMIT; }
+
+# Violates referential integrity unless we use a crosscheck snapshot,
+# which is up-to-date compared with the transaction's snapshot.
+permutation s1rr s2rr s2ins s1del s2c s1c
+
+# Raises a can't-serialize exception
+# when the INSERT trigger does SELECT FOR KEY SHARE:
+permutation s1rr s2rr s1del s2ins s1c s2c
+
+# Test the same scenarios in READ COMMITTED:
+# A crosscheck snapshot is not required here.
+permutation s1rc s2rc s2ins s1del s2c s1c
+permutation s1rc s2rc s1del s2ins s1c s2c
+
+# Test the same scenarios in SERIALIZABLE:
+# We should report the FK violation:
+permutation s1ser s2ser s2ins s1del s2c s1c
+# We raise a concurrent update error
+# which is good enough:
+permutation s1ser s2ser s1del s2ins s1c s2c
--
2.39.5
From 5f216d3cd8a69d00567d521645ccb376fcea6a79 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 15 May 2025 10:33:04 -0400
Subject: [PATCH v3 3/3] Improve comment about snapshot macros
The comment mistakenly had "the others" for "the other", but this
commit also reorders the comment so it matches the macros below. Now we
describe the levels in increasing strictness. Finally, it seems easier
to follow if we introduce one level at a time, rather than describing
two, followed by "the other" (and then jumping back to one of the first
two).
---
src/include/access/xact.h | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index b2bc10ee041..fdcff3411ad 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -43,8 +43,8 @@ extern PGDLLIMPORT int XactIsoLevel;
/*
* We implement three isolation levels internally.
- * The two stronger ones use one snapshot per database transaction;
- * the others use one snapshot per statement.
+ * The weakest uses one snapshot per statement;
+ * the two stronger levels use one snapshot per database transaction.
* Serializable uses predicate locks in addition to snapshots.
* These macros should be used to check which isolation level is selected.
*/
--
2.39.5
From 433d2997824d64d4396e4ba744d93bdbf8996702 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 15 May 2025 10:43:19 -0400
Subject: [PATCH v3 2/3] Add test for temporal referential integrity
This commit adds an isolation test showing that temporal foreign keys
do not permit referential integrity violations under concurrency, like
fk-snapshot-2. You can show that the test fails by passing false for
detectNewRows in ri_restrict.
I also included tests for changing the valid_at parent value (both so as
to leave the child reference valid, and it make it invalid).
---
src/test/isolation/expected/fk-snapshot-3.out | 213 ++++++++++++++++++
src/test/isolation/isolation_schedule | 120 +---------
src/test/isolation/specs/fk-snapshot-3.spec | 82 +++++++
3 files changed, 296 insertions(+), 119 deletions(-)
create mode 100644 src/test/isolation/expected/fk-snapshot-3.out
create mode 100644 src/test/isolation/specs/fk-snapshot-3.spec
diff --git a/src/test/isolation/expected/fk-snapshot-3.out b/src/test/isolation/expected/fk-snapshot-3.out
new file mode 100644
index 00000000000..f98cb72fdac
--- /dev/null
+++ b/src/test/isolation/expected/fk-snapshot-3.out
@@ -0,0 +1,213 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1rr s2rr s2ins s1del s2c s1c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1del: DELETE FROM parent WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rr s2rr s1del s2ins s1c s2c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1del: DELETE FROM parent WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1rc s2rc s2ins s1del s2c s1c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1del: DELETE FROM parent WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rc s2rc s1del s2ins s1c s2c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s1del: DELETE FROM parent WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: insert or update on table "child" violates foreign key constraint "child_parent_id_valid_at_fkey"
+step s2c: COMMIT;
+
+starting permutation: s1ser s2ser s2ins s1del s2c s1c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1del: DELETE FROM parent WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1del: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1ser s2ser s1del s2ins s1c s2c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1del: DELETE FROM parent WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1rc s2rc s2ins s1upok s2c s1c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upok: <... completed>
+step s1c: COMMIT;
+
+starting permutation: s1rc s2rc s1upok s2ins s1c s2c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+step s2c: COMMIT;
+
+starting permutation: s1rr s2rr s2ins s1upok s2c s1c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upok: <... completed>
+step s1c: COMMIT;
+
+starting permutation: s1rr s2rr s1upok s2ins s1c s2c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1ser s2ser s2ins s1upok s2c s1c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upok: <... completed>
+step s1c: COMMIT;
+
+starting permutation: s1ser s2ser s1upok s2ins s1c s2c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1upok: UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1rc s2rc s2ins s1upbad s2c s1c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upbad: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rc s2rc s1upbad s2ins s1c s2c
+step s1rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2rc: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: insert or update on table "child" violates foreign key constraint "child_parent_id_valid_at_fkey"
+step s2c: COMMIT;
+
+starting permutation: s1rr s2rr s2ins s1upbad s2c s1c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upbad: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1rr s2rr s1upbad s2ins s1c s2c
+step s1rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2rr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+
+starting permutation: s1ser s2ser s2ins s1upbad s2c s1c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)'; <waiting ...>
+step s2c: COMMIT;
+step s1upbad: <... completed>
+ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_valid_at_fkey" on table "child"
+step s1c: COMMIT;
+
+starting permutation: s1ser s2ser s1upbad s2ins s1c s2c
+step s1ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s2ser: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1upbad: UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)';
+step s2ins:
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+ <waiting ...>
+step s1c: COMMIT;
+step s2ins: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 12b6581d5ab..a1fbee1c684 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -1,119 +1 @@
-test: read-only-anomaly
-test: read-only-anomaly-2
-test: read-only-anomaly-3
-test: read-write-unique
-test: read-write-unique-2
-test: read-write-unique-3
-test: read-write-unique-4
-test: simple-write-skew
-test: receipt-report
-test: temporal-range-integrity
-test: project-manager
-test: classroom-scheduling
-test: total-cash
-test: referential-integrity
-test: ri-trigger
-test: partial-index
-test: two-ids
-test: multiple-row-versions
-test: index-only-scan
-test: index-only-bitmapscan
-test: predicate-lock-hot-tuple
-test: update-conflict-out
-test: deadlock-simple
-test: deadlock-hard
-test: deadlock-soft
-test: deadlock-soft-2
-test: deadlock-parallel
-test: detach-partition-concurrently-1
-test: detach-partition-concurrently-2
-test: detach-partition-concurrently-3
-test: detach-partition-concurrently-4
-test: fk-contention
-test: fk-deadlock
-test: fk-deadlock2
-test: fk-partitioned-1
-test: fk-partitioned-2
-test: fk-snapshot
-test: fk-snapshot-2
-test: subxid-overflow
-test: eval-plan-qual
-test: eval-plan-qual-trigger
-test: inplace-inval
-test: intra-grant-inplace
-test: intra-grant-inplace-db
-test: lock-update-delete
-test: lock-update-traversal
-test: inherit-temp
-test: temp-schema-cleanup
-test: insert-conflict-do-nothing
-test: insert-conflict-do-nothing-2
-test: insert-conflict-do-update
-test: insert-conflict-do-update-2
-test: insert-conflict-do-update-3
-test: insert-conflict-specconflict
-test: merge-insert-update
-test: merge-delete
-test: merge-update
-test: merge-match-recheck
-test: merge-join
-test: delete-abort-savept
-test: delete-abort-savept-2
-test: aborted-keyrevoke
-test: multixact-no-deadlock
-test: multixact-no-forget
-test: lock-committed-update
-test: lock-committed-keyupdate
-test: update-locked-tuple
-test: reindex-concurrently
-test: reindex-concurrently-toast
-test: reindex-schema
-test: propagate-lock-delete
-test: tuplelock-conflict
-test: tuplelock-update
-test: tuplelock-upgrade-no-deadlock
-test: tuplelock-partition
-test: freeze-the-dead
-test: nowait
-test: nowait-2
-test: nowait-3
-test: nowait-4
-test: nowait-5
-test: skip-locked
-test: skip-locked-2
-test: skip-locked-3
-test: skip-locked-4
-test: drop-index-concurrently-1
-test: multiple-cic
-test: alter-table-1
-test: alter-table-2
-test: alter-table-3
-test: alter-table-4
-test: create-trigger
-test: sequence-ddl
-test: async-notify
-test: vacuum-no-cleanup-lock
-test: timeouts
-test: vacuum-concurrent-drop
-test: vacuum-conflict
-test: vacuum-skip-locked
-test: stats
-test: horizons
-test: predicate-hash
-test: predicate-gist
-test: predicate-gin
-test: partition-concurrent-attach
-test: partition-drop-index-locking
-test: partition-key-update-1
-test: partition-key-update-2
-test: partition-key-update-3
-test: partition-key-update-4
-test: plpgsql-toast
-test: cluster-conflict
-test: cluster-conflict-partition
-test: truncate-conflict
-test: serializable-parallel
-test: serializable-parallel-2
-test: serializable-parallel-3
-test: matview-write-skew
-test: lock-nowait
+test: fk-snapshot-3
diff --git a/src/test/isolation/specs/fk-snapshot-3.spec b/src/test/isolation/specs/fk-snapshot-3.spec
new file mode 100644
index 00000000000..90075024f5c
--- /dev/null
+++ b/src/test/isolation/specs/fk-snapshot-3.spec
@@ -0,0 +1,82 @@
+# RI Trigger test
+#
+# Test C-based temporal referential integrity enforcement.
+# Under REPEATABLE READ we need some snapshot trickery in C,
+# or we would permit things that violate referential integrity.
+
+setup
+{
+ CREATE TABLE parent (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS));
+ CREATE TABLE child (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ parent_id int4range,
+ FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES parent);
+ INSERT INTO parent VALUES ('[1,2)', '[2020-01-01,2030-01-01)');
+}
+
+teardown { DROP TABLE parent, child; }
+
+session s1
+step s1rc { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s1rr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s1ser { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step s1del { DELETE FROM parent WHERE id = '[1,2)'; }
+step s1upok { UPDATE parent SET valid_at = '[2020-01-01,2026-01-01)' WHERE id = '[1,2)'; }
+step s1upbad { UPDATE parent SET valid_at = '[2020-01-01,2024-01-01)' WHERE id = '[1,2)'; }
+step s1c { COMMIT; }
+
+session s2
+step s2rc { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s2rr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s2ser { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step s2ins {
+ INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2025-01-01)', '[1,2)');
+}
+step s2c { COMMIT; }
+
+# Violates referential integrity unless we use an up-to-date crosscheck snapshot:
+permutation s1rr s2rr s2ins s1del s2c s1c
+
+# Raises a can't-serialize exception
+# when the INSERT trigger does SELECT FOR KEY SHARE:
+permutation s1rr s2rr s1del s2ins s1c s2c
+
+# Test the same scenarios in READ COMMITTED:
+# A crosscheck snapshot is not required here.
+permutation s1rc s2rc s2ins s1del s2c s1c
+permutation s1rc s2rc s1del s2ins s1c s2c
+
+# Test the same scenarios in SERIALIZABLE:
+# We should report the FK violation:
+permutation s1ser s2ser s2ins s1del s2c s1c
+# We raise a concurrent update error
+# which is good enough:
+permutation s1ser s2ser s1del s2ins s1c s2c
+
+# Also check updating the valid time (without violating RI):
+
+# ...with READ COMMITED:
+permutation s1rc s2rc s2ins s1upok s2c s1c
+permutation s1rc s2rc s1upok s2ins s1c s2c
+# ...with REPEATABLE READ:
+permutation s1rr s2rr s2ins s1upok s2c s1c
+permutation s1rr s2rr s1upok s2ins s1c s2c
+# ...with SERIALIZABLE:
+permutation s1ser s2ser s2ins s1upok s2c s1c
+permutation s1ser s2ser s1upok s2ins s1c s2c
+
+# Also check updating the valid time (while violating RI):
+
+# ...with READ COMMITED:
+permutation s1rc s2rc s2ins s1upbad s2c s1c
+permutation s1rc s2rc s1upbad s2ins s1c s2c
+# ...with REPEATABLE READ:
+permutation s1rr s2rr s2ins s1upbad s2c s1c
+permutation s1rr s2rr s1upbad s2ins s1c s2c
+# ...with SERIALIZABLE:
+permutation s1ser s2ser s2ins s1upbad s2c s1c
+permutation s1ser s2ser s1upbad s2ins s1c s2c
--
2.39.5
