From 46651ce6fe013220ed397add242004d764fc0153 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sat, 4 May 2024 14:15:05 +0200
Subject: Adding upstream version 14.5.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 src/test/isolation/specs/ri-trigger.spec | 53 ++++++++++++++++++++++++++++++++
 1 file changed, 53 insertions(+)
 create mode 100644 src/test/isolation/specs/ri-trigger.spec

(limited to 'src/test/isolation/specs/ri-trigger.spec')

diff --git a/src/test/isolation/specs/ri-trigger.spec b/src/test/isolation/specs/ri-trigger.spec
new file mode 100644
index 0000000..00fcdff
--- /dev/null
+++ b/src/test/isolation/specs/ri-trigger.spec
@@ -0,0 +1,53 @@
+# RI Trigger test
+#
+# Test trigger-based referential integrity enforcement.
+#
+# Any overlap between the transactions must cause a serialization failure.
+
+setup
+{
+ CREATE TABLE parent (parent_id SERIAL NOT NULL PRIMARY KEY);
+ CREATE TABLE child (child_id SERIAL NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL);
+ CREATE FUNCTION ri_parent() RETURNS TRIGGER LANGUAGE PLPGSQL AS $body$
+ BEGIN
+  PERFORM TRUE FROM child WHERE parent_id = OLD.parent_id;
+  IF FOUND THEN
+    RAISE SQLSTATE '23503' USING MESSAGE = 'child row exists';
+  END IF;
+  IF TG_OP = 'DELETE' THEN
+    RETURN OLD;
+  END IF;
+  RETURN NEW;
+ END;
+ $body$;
+ CREATE TRIGGER ri_parent BEFORE UPDATE OR DELETE ON parent FOR EACH ROW EXECUTE PROCEDURE ri_parent();
+ CREATE FUNCTION ri_child() RETURNS TRIGGER LANGUAGE PLPGSQL AS $body$
+ BEGIN
+  PERFORM TRUE FROM parent WHERE parent_id = NEW.parent_id;
+  IF NOT FOUND THEN
+    RAISE SQLSTATE '23503' USING MESSAGE = 'parent row missing';
+  END IF;
+  RETURN NEW;
+ END;
+ $body$;
+ CREATE TRIGGER ri_child BEFORE INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE ri_child();
+ INSERT INTO parent VALUES(0);
+}
+
+teardown
+{
+ DROP TABLE parent, child;
+ DROP FUNCTION ri_parent();
+ DROP FUNCTION ri_child();
+}
+
+session s1
+setup		{ BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step wxry1	{ INSERT INTO child (parent_id) VALUES (0); }
+step c1		{ COMMIT; }
+
+session s2
+setup		{ BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step r2		{ SELECT TRUE; }
+step wyrx2	{ DELETE FROM parent WHERE parent_id = 0; }
+step c2		{ COMMIT; }
-- 
cgit v1.2.3