From 995002793b0af57b660565e6027615c083d8ce5e Mon Sep 17 00:00:00 2001
From: Daniil Davidov <davydovdaniil501@gmail.com>
Date: Mon, 28 Oct 2024 17:19:07 +0700
Subject: [PATCH] Add isolaton test for work with other session's temp table

---
 src/test/isolation/expected/temp-tables.out |  75 +++++++++++++++
 src/test/isolation/isolation_schedule       |   1 +
 src/test/isolation/specs/temp-tables.spec   | 100 ++++++++++++++++++++
 3 files changed, 176 insertions(+)
 create mode 100644 src/test/isolation/expected/temp-tables.out
 create mode 100644 src/test/isolation/specs/temp-tables.spec

diff --git a/src/test/isolation/expected/temp-tables.out b/src/test/isolation/expected/temp-tables.out
new file mode 100644
index 0000000000..dbd6ffc2e5
--- /dev/null
+++ b/src/test/isolation/expected/temp-tables.out
@@ -0,0 +1,75 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_st1 s1_st2 s2_st1 s1_st3 s2_st2
+step s1_st1: CREATE TEMP TABLE test_tmp(id int);
+step s1_st2: INSERT INTO temp_schema_id SELECT pg_my_temp_schema();
+s2: NOTICE:  cannot access temporary tables of other sessions
+s2: NOTICE:  cannot access temporary tables of other sessions
+step s2_st1: 
+    DO $$
+        DECLARE
+            schema_name text;
+        BEGIN
+            -- Find out name of temporary schema of first session
+            SELECT nspname INTO schema_name
+              FROM pg_namespace
+             WHERE oid = (SELECT oid FROM temp_schema_id LIMIT 1);
+
+            -- Execute few insert operations. We expect that behavior to be the
+            -- same (both will complete successfully or both will fail). Since
+            -- we have RELATION_IS_OTHER_TEMP() check in PrefetchBuffer and
+            -- ReadBufferExtended functions (src/backend/storage/buffer/bufmgr.c),
+            -- let's assume that both operations must fail (this is reflected
+            -- in expected file)
+            BEGIN
+                EXECUTE format('INSERT INTO %I.test_tmp VALUES (1);', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+
+            BEGIN
+                EXECUTE format('INSERT INTO %I.test_tmp VALUES (2);', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+        END
+    $$;
+
+step s1_st3: INSERT INTO test_tmp VALUES (3);
+s2: NOTICE:  (3)
+s2: NOTICE:  cannot access temporary tables of other sessions
+s2: NOTICE:  cannot access temporary tables of other sessions
+step s2_st2: 
+    DO $$
+        DECLARE
+            schema_name text;
+            result RECORD;
+        BEGIN
+            -- Find out name of temporary schema of first session
+            SELECT nspname INTO schema_name
+              FROM pg_namespace
+             WHERE oid = (SELECT oid FROM temp_schema_id LIMIT 1);
+
+            -- Before this step call, first session inserted few tuples into
+            -- test_tmp table. Let's assume that SELECT result must contain all
+            -- of these tuples (based on current logic)
+            FOR result IN
+                       EXECUTE format('SELECT * FROM %I.test_tmp;', schema_name)
+                          LOOP
+                               RAISE NOTICE '%', result;
+                           END LOOP;
+
+            -- Now lets try to update or delete tuples from test_tmp. If we
+            -- cannot insert into this table, lets assume that both UPDATE and
+            -- DELETE operations must return same error as INSERT
+            BEGIN
+                EXECUTE format('UPDATE %I.test_tmp SET id = 100 WHERE id = 3;', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+        END
+    $$;
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 143109aa4d..8ed3f821a4 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -115,3 +115,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: temp-tables
\ No newline at end of file
diff --git a/src/test/isolation/specs/temp-tables.spec b/src/test/isolation/specs/temp-tables.spec
new file mode 100644
index 0000000000..e77c871c79
--- /dev/null
+++ b/src/test/isolation/specs/temp-tables.spec
@@ -0,0 +1,100 @@
+# Test access to temporary tables of other sessions
+
+# We need to know oid of first session's temporary schema
+setup { CREATE TABLE temp_schema_id(oid oid); }
+
+teardown { DROP TABLE temp_schema_id; }
+
+session s1
+
+# In first session we only need to create temporary table and "remember" its oid
+step s1_st1 { CREATE TEMP TABLE test_tmp(id int); }
+step s1_st2 { INSERT INTO temp_schema_id SELECT pg_my_temp_schema(); }
+step s1_st3 { INSERT INTO test_tmp VALUES (3); }
+
+session s2
+
+# In this DO block we will try to access to temp table created before via
+# INSERT operation
+step s2_st1
+{
+    DO $$
+        DECLARE
+            schema_name text;
+        BEGIN
+            -- Find out name of temporary schema of first session
+            SELECT nspname INTO schema_name
+              FROM pg_namespace
+             WHERE oid = (SELECT oid FROM temp_schema_id LIMIT 1);
+
+            -- Execute few insert operations. We expect that behavior to be the
+            -- same (both will complete successfully or both will fail). Since
+            -- we have RELATION_IS_OTHER_TEMP() check in PrefetchBuffer and
+            -- ReadBufferExtended functions (src/backend/storage/buffer/bufmgr.c),
+            -- let's assume that both operations must fail (this is reflected
+            -- in expected file)
+            BEGIN
+                EXECUTE format('INSERT INTO %I.test_tmp VALUES (1);', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+
+            BEGIN
+                EXECUTE format('INSERT INTO %I.test_tmp VALUES (2);', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+        END
+    $$;
+}
+
+# In this DO block we will try to access to temp table created before via
+# SELECT/UPDATE/DELETE operations
+step s2_st2
+{
+    DO $$
+        DECLARE
+            schema_name text;
+            result RECORD;
+        BEGIN
+            -- Find out name of temporary schema of first session
+            SELECT nspname INTO schema_name
+              FROM pg_namespace
+             WHERE oid = (SELECT oid FROM temp_schema_id LIMIT 1);
+
+            -- Before this step call, first session inserted few tuples into
+            -- test_tmp table. Let's assume that SELECT result must contain all
+            -- of these tuples (based on current logic)
+            FOR result IN
+                EXECUTE format('SELECT * FROM %I.test_tmp;', schema_name) LOOP
+                    RAISE NOTICE '%', result;
+                END LOOP;
+
+            -- Now lets try to update or delete tuples from test_tmp. If we
+            -- cannot insert into this table, lets assume that both UPDATE and
+            -- DELETE operations must return same error as INSERT
+            BEGIN
+                EXECUTE format('UPDATE %I.test_tmp SET id = 100 WHERE id = 3;', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+
+            BEGIN
+                EXECUTE format('DELETE FROM %I.test_tmp WHERE id = 3;', schema_name);
+            EXCEPTION
+                WHEN feature_not_supported
+                THEN RAISE NOTICE 'cannot access temporary tables of other sessions';
+            END;
+        END
+    $$;
+}
+
+permutation
+    s1_st1
+    s1_st2
+    s2_st1
+    s1_st3
+    s2_st2
\ No newline at end of file
-- 
2.43.0

