v2-0001-Add-test-case-that-triggers-self-referencing-table-c.patch

From 534002b0999c6e620c055f3e52634937c4693849 Mon Sep 17 00:00:00 2001
From: luquijeffrey <lucas.jeffrey@anachronics.com>
Date: Fri, 29 May 2026 12:23:38 -0300
Subject: [PATCH 1/2] =?UTF-8?q?Add=20test=20case=20that=20triggers=20self?=
 =?UTF-8?q?=E2=80=91referencing=20table=20crash=20(patch1)?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 .../isolation/expected/ri-cascade-del.out     | 27 ++++++
 src/test/isolation/isolation_schedule         |  1 +
 src/test/isolation/specs/ri-cascade-del.spec  | 83 +++++++++++++++++++
 3 files changed, 111 insertions(+)
 create mode 100644 src/test/isolation/expected/ri-cascade-del.out
 create mode 100644 src/test/isolation/specs/ri-cascade-del.spec

diff --git a/src/test/isolation/expected/ri-cascade-del.out b/src/test/isolation/expected/ri-cascade-del.out
new file mode 100644
index 00000000000..bd7ae0b0b5a
--- /dev/null
+++ b/src/test/isolation/expected/ri-cascade-del.out
@@ -0,0 +1,27 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s2_lock s1_delete s2_inval s2_unlock
+step s2_lock: SELECT pg_advisory_lock(0);
+pg_advisory_lock
+----------------
+                
+(1 row)
+
+step s1_delete: DELETE FROM crash_reentrancia_tabla_autoreferencial WHERE id = 1; <waiting ...>
+step s2_inval: 
+  DO $$                                    
+  BEGIN
+    FOR i IN 1..1000 LOOP
+      EXECUTE 'CREATE TEMPORARY TABLE t_temp_inval_(id INTEGER PRIMARY KEY)';
+      EXECUTE 'DROP TABLE t_temp_inval_';
+    END LOOP;
+  END;
+  $$;
+
+step s2_unlock: SELECT pg_advisory_unlock(0);
+pg_advisory_unlock
+------------------
+t                 
+(1 row)
+
+step s1_delete: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 1578ba191c8..39a0a1ee792 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -12,6 +12,7 @@ test: project-manager
 test: classroom-scheduling
 test: total-cash
 test: referential-integrity
+test: ri-cascade-del
 test: ri-trigger
 test: partial-index
 test: two-ids
diff --git a/src/test/isolation/specs/ri-cascade-del.spec b/src/test/isolation/specs/ri-cascade-del.spec
new file mode 100644
index 00000000000..c412ec2c772
--- /dev/null
+++ b/src/test/isolation/specs/ri-cascade-del.spec
@@ -0,0 +1,83 @@
+# Setup for referential integrity crash test
+setup
+{
+    CREATE TABLE crash_reentrancia_tabla_autoreferencial (
+        id int PRIMARY KEY,
+        nombre text,
+        padre_id int REFERENCES crash_reentrancia_tabla_autoreferencial(id) ON DELETE CASCADE
+    );
+
+    CREATE TABLE crash_reentrancia_segunda_tabla (
+        id    int PRIMARY KEY,
+        valor text
+    );
+
+    CREATE OR REPLACE FUNCTION crash_reentrancia_before_delete()
+    RETURNS trigger AS $$
+    DECLARE
+        v_valor text;
+    BEGIN
+        IF OLD.id % 2 = 1 THEN 
+            RETURN OLD;
+        END IF;
+
+        -- Wait for S2 to finish flooding the invalidation message queue
+        IF OLD.id = 2 THEN
+            PERFORM pg_advisory_lock(0);
+            PERFORM pg_advisory_unlock(0);
+        END IF;
+
+        IF OLD.id > 4 THEN
+            -- This opens the table and forces processing of pending inval messages
+            SELECT valor INTO v_valor FROM crash_reentrancia_segunda_tabla WHERE id = OLD.id;
+        END IF;
+        
+        DELETE FROM crash_reentrancia_tabla_autoreferencial WHERE padre_id = OLD.id;
+        RETURN OLD;
+    END;
+    $$ LANGUAGE plpgsql;
+
+    CREATE TRIGGER trg_crash_reentrancia_before_delete
+        BEFORE DELETE ON crash_reentrancia_tabla_autoreferencial
+        FOR EACH ROW EXECUTE FUNCTION crash_reentrancia_before_delete();
+
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (1, 'A', NULL);
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (2, 'B', 1);
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (3, 'C', 2);
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (4, 'D', 3);
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (5, 'E', 4);
+    INSERT INTO crash_reentrancia_tabla_autoreferencial VALUES (6, 'F', 5);
+
+    INSERT INTO crash_reentrancia_segunda_tabla VALUES 
+        (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), (6, 'f');
+}
+
+teardown
+{
+    DROP TRIGGER trg_crash_reentrancia_before_delete ON crash_reentrancia_tabla_autoreferencial;
+    DROP FUNCTION crash_reentrancia_before_delete CASCADE;
+    DROP TABLE crash_reentrancia_tabla_autoreferencial CASCADE;
+    DROP TABLE crash_reentrancia_segunda_tabla CASCADE;
+}
+
+session s1
+step s1_delete { DELETE FROM crash_reentrancia_tabla_autoreferencial WHERE id = 1; }
+
+session s2
+step s2_lock { SELECT pg_advisory_lock(0); }
+step s2_inval {
+  DO $$                                    
+  BEGIN
+    FOR i IN 1..1000 LOOP
+      EXECUTE 'CREATE TEMPORARY TABLE t_temp_inval_(id INTEGER PRIMARY KEY)';
+      EXECUTE 'DROP TABLE t_temp_inval_';
+    END LOOP;
+  END;
+  $$;
+}
+step s2_unlock { SELECT pg_advisory_unlock(0); }
+
+# Execution permutation
+# S2 locks -> S1 blocks on S2 -> S2 forces inval queue overflow -> S2 unlocks
+# S1 awakens -> S1 forces table_open -> invalidation processed -> segfault!
+permutation s2_lock s1_delete s2_inval s2_unlock
-- 
2.34.1