v3-0002-Add-test-for-temporal-referential-integrity.patch

From 433d2997824d64d4396e4ba744d93bdbf8996702 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@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