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

From 932ba6b1f198955d4d855a876afd98f0c067d939 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 v1 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.
---
 src/test/isolation/expected/fk-snapshot-3.out | 21 ++++++++++
 src/test/isolation/isolation_schedule         |  1 +
 src/test/isolation/specs/fk-snapshot-3.spec   | 40 +++++++++++++++++++
 3 files changed, 62 insertions(+)
 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..7d1b9b7eedc
--- /dev/null
+++ b/src/test/isolation/expected/fk-snapshot-3.out
@@ -0,0 +1,21 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s2ins s1del s2c s1c
+step s2ins: 
+  INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2030-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: s1del s2ins s1c s2c
+step s1del: DELETE FROM parent WHERE id = '[1,2)';
+step s2ins: 
+  INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2030-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..bd903bee823 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -36,6 +36,7 @@ test: fk-partitioned-1
 test: fk-partitioned-2
 test: fk-snapshot
 test: fk-snapshot-2
+test: fk-snapshot-3
 test: subxid-overflow
 test: eval-plan-qual
 test: eval-plan-qual-trigger
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..6ae32ecf2fc
--- /dev/null
+++ b/src/test/isolation/specs/fk-snapshot-3.spec
@@ -0,0 +1,40 @@
+# 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
+setup		{ BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s1del	{ DELETE FROM parent WHERE id = '[1,2)'; }
+step s1c	{ COMMIT; }
+
+session s2
+setup		{ BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s2ins	{
+  INSERT INTO child VALUES ('[1,2)', '[2020-01-01,2030-01-01)', '[1,2)');
+}
+step s2c	{ COMMIT; }
+
+# Violates referential integrity unless we use an up-to-date crosscheck snapshot:
+permutation s2ins s1del s2c s1c
+
+# Raises a can't-serialize exception
+# when the INSERT trigger does SELECT FOR KEY SHARE:
+permutation s1del s2ins s1c s2c
-- 
2.45.0