v20250603-2-0015-plpgsql-tests.patch

From 6242d3640477e4e5800bac61c7112e45c60eab78 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Wed, 13 Nov 2024 14:06:06 +0100
Subject: [PATCH 15/15] plpgsql tests

set of plpgsql related tests:

* check session variables and plpgsql variables are not in collision ever
* check correct plpgsql plan cache invalidation when session variable is dropped
* check so the value of session variable is not corrupted, when the variable is
  modified inside nested called functions
---
 src/pl/plpgsql/src/Makefile                   |   3 +-
 .../src/expected/plpgsql_session_variable.out | 198 ++++++++++++++++++
 src/pl/plpgsql/src/meson.build                |   1 +
 .../src/sql/plpgsql_session_variable.sql      | 137 ++++++++++++
 4 files changed, 338 insertions(+), 1 deletion(-)
 create mode 100644 src/pl/plpgsql/src/expected/plpgsql_session_variable.out
 create mode 100644 src/pl/plpgsql/src/sql/plpgsql_session_variable.sql

diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile
index 63cb96fae3e..bbcae27d422 100644
--- a/src/pl/plpgsql/src/Makefile
+++ b/src/pl/plpgsql/src/Makefile
@@ -35,7 +35,8 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB)
 REGRESS = plpgsql_array plpgsql_cache plpgsql_call plpgsql_control \
 	plpgsql_copy plpgsql_domain plpgsql_misc \
 	plpgsql_record plpgsql_simple plpgsql_transaction \
-	plpgsql_trap plpgsql_trigger plpgsql_varprops
+	plpgsql_trap plpgsql_trigger plpgsql_varprops \
+	plpgsql_session_variable
 
 # where to find gen_keywordlist.pl and subsidiary files
 TOOLSDIR = $(top_srcdir)/src/tools
diff --git a/src/pl/plpgsql/src/expected/plpgsql_session_variable.out b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out
new file mode 100644
index 00000000000..1dcec78c234
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out
@@ -0,0 +1,198 @@
+-- check of correct plan cache invalidation
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+CREATE VARIABLE plpgsql_sesvar02 AS int[];
+-- plpgsql variables and session variables are not in collision ever
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_01()
+RETURNS void AS $$
+DECLARE plpgsql_sesvar01 int;
+BEGIN
+  plpgsql_sesvar01 := 100;
+  LET plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    plpgsql_sesvar01, VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+SELECT svartest_plpgsql_func01_01();
+NOTICE:  plpgsql var: 100, session var: 1000
+ svartest_plpgsql_func01_01 
+----------------------------
+ 
+(1 row)
+
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_02()
+RETURNS void AS $$
+DECLARE __plpgsql_sesvar01 int;
+BEGIN
+  __plpgsql_sesvar01 := 100;
+  LET __plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    __plpgsql_sesvar01, VARIABLE(__plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+-- should fail
+SELECT svartest_plpgsql_func01_02();
+ERROR:  session variable "__plpgsql_sesvar01" doesn't exist
+LINE 1: LET __plpgsql_sesvar01 = 1000
+            ^
+QUERY:  LET __plpgsql_sesvar01 = 1000
+CONTEXT:  PL/pgSQL function svartest_plpgsql_func01_02() line 5 at SQL statement
+-- should fail
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_03()
+RETURNS void AS $$
+BEGIN
+  plpgsql_sesvar01 := 100;
+  LET plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    plpgsql_sesvar01, VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+ERROR:  "plpgsql_sesvar01" is not a known variable
+LINE 4:   plpgsql_sesvar01 := 100;
+          ^
+DROP FUNCTION svartest_plpgsql_func01_01();
+DROP FUNCTION svartest_plpgsql_func01_02();
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func02()
+RETURNS void AS $$
+DECLARE v int[] DEFAULT '{}';
+BEGIN
+  LET plpgsql_sesvar01 = 1;
+  v[VARIABLE(plpgsql_sesvar01)] = 100;
+  RAISE NOTICE '%', v;
+  LET plpgsql_sesvar02 = v;
+  LET plpgsql_sesvar02[VARIABLE(plpgsql_sesvar01)] = -1;
+  RAISE NOTICE '%', VARIABLE(plpgsql_sesvar02);
+END;
+$$ LANGUAGE plpgsql;
+SELECT svartest_plpgsql_func02();
+NOTICE:  {100}
+NOTICE:  {-1}
+ svartest_plpgsql_func02 
+-------------------------
+ 
+(1 row)
+
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+CREATE VARIABLE plpgsql_sesvar02 AS int[];
+SELECT svartest_plpgsql_func02();
+NOTICE:  {100}
+NOTICE:  {-1}
+ svartest_plpgsql_func02 
+-------------------------
+ 
+(1 row)
+
+DROP FUNCTION svartest_plpgsql_func02();
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+-- returns updated value
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+CREATE OR REPLACE FUNCTION svartest_plpgsql_inc(int)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sesvar01 = COALESCE(VARIABLE(plpgsql_sesvar01) + $1, $1);
+  RETURN VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+SELECT svartest_plpgsql_inc(1);
+ svartest_plpgsql_inc 
+----------------------
+                    1
+(1 row)
+
+SELECT svartest_plpgsql_inc(1);
+ svartest_plpgsql_inc 
+----------------------
+                    2
+(1 row)
+
+SELECT svartest_plpgsql_inc(1);
+ svartest_plpgsql_inc 
+----------------------
+                    3
+(1 row)
+
+SELECT svartest_plpgsql_inc(1) FROM generate_series(1,10);
+ svartest_plpgsql_inc 
+----------------------
+                    4
+                    5
+                    6
+                    7
+                    8
+                    9
+                   10
+                   11
+                   12
+                   13
+(10 rows)
+
+CREATE VARIABLE plpgsql_sesvar02 AS numeric;
+LET plpgsql_sesvar02 = 0.0;
+CREATE OR REPLACE FUNCTION svartest_plpgsql_inc(numeric)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sesvar02 = COALESCE(VARIABLE(plpgsql_sesvar02) + $1, $1);
+  RETURN VARIABLE(plpgsql_sesvar02);
+END;
+$$ LANGUAGE plpgsql;
+SELECT svartest_plpgsql_inc(1.0);
+ svartest_plpgsql_inc 
+----------------------
+                    1
+(1 row)
+
+SELECT svartest_plpgsql_inc(1.0);
+ svartest_plpgsql_inc 
+----------------------
+                    2
+(1 row)
+
+SELECT svartest_plpgsql_inc(1.0);
+ svartest_plpgsql_inc 
+----------------------
+                    3
+(1 row)
+
+SELECT svartest_plpgsql_inc(1.0) FROM generate_series(1,10);
+ svartest_plpgsql_inc 
+----------------------
+                    4
+                    5
+                    6
+                    7
+                    8
+                    9
+                   10
+                   11
+                   12
+                   13
+(10 rows)
+
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+DROP FUNCTION svartest_plpgsql_inc(int);
+DROP FUNCTION svartest_plpgsql_inc(numeric);
+-- the value should not be corrupted
+CREATE VARIABLE plpgsql_sesvar03 text;
+LET plpgsql_sesvar03 = 'abc';
+CREATE FUNCTION svartest_plpgsql_func03()
+RETURNS text AS $$
+BEGIN
+  RETURN svartest_plpgsql_func_nested(VARIABLE(plpgsql_sesvar03));
+END
+$$ LANGUAGE plpgsql;
+CREATE FUNCTION svartest_plpgsql_func_nested(t text)
+RETURNS text AS $$
+BEGIN
+  LET plpgsql_sesvar03 = 'BOOM!';
+  RETURN t;
+END;
+$$ LANGUAGE plpgsql;
+SELECT svartest_plpgsql_func03();
+ svartest_plpgsql_func03 
+-------------------------
+ abc
+(1 row)
+
+DROP FUNCTION svartest_plpgsql_func03();
+DROP FUNCTION svartest_plpgsql_func_nested(text);
+DROP VARIABLE plpgsql_sesvar03;
diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build
index 33c49ac25d9..1d01d1c2629 100644
--- a/src/pl/plpgsql/src/meson.build
+++ b/src/pl/plpgsql/src/meson.build
@@ -88,6 +88,7 @@ tests += {
       'plpgsql_trap',
       'plpgsql_trigger',
       'plpgsql_varprops',
+      'plpgsql_session_variable',
     ],
   },
 }
diff --git a/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql
new file mode 100644
index 00000000000..5abf18e3bb3
--- /dev/null
+++ b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql
@@ -0,0 +1,137 @@
+-- check of correct plan cache invalidation
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+CREATE VARIABLE plpgsql_sesvar02 AS int[];
+
+-- plpgsql variables and session variables are not in collision ever
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_01()
+RETURNS void AS $$
+DECLARE plpgsql_sesvar01 int;
+BEGIN
+  plpgsql_sesvar01 := 100;
+  LET plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    plpgsql_sesvar01, VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT svartest_plpgsql_func01_01();
+
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_02()
+RETURNS void AS $$
+DECLARE __plpgsql_sesvar01 int;
+BEGIN
+  __plpgsql_sesvar01 := 100;
+  LET __plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    __plpgsql_sesvar01, VARIABLE(__plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+
+-- should fail
+SELECT svartest_plpgsql_func01_02();
+
+-- should fail
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func01_03()
+RETURNS void AS $$
+BEGIN
+  plpgsql_sesvar01 := 100;
+  LET plpgsql_sesvar01 = 1000;
+  RAISE NOTICE 'plpgsql var: %, session var: %',
+    plpgsql_sesvar01, VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+
+DROP FUNCTION svartest_plpgsql_func01_01();
+DROP FUNCTION svartest_plpgsql_func01_02();
+
+CREATE OR REPLACE FUNCTION svartest_plpgsql_func02()
+RETURNS void AS $$
+DECLARE v int[] DEFAULT '{}';
+BEGIN
+  LET plpgsql_sesvar01 = 1;
+  v[VARIABLE(plpgsql_sesvar01)] = 100;
+  RAISE NOTICE '%', v;
+  LET plpgsql_sesvar02 = v;
+  LET plpgsql_sesvar02[VARIABLE(plpgsql_sesvar01)] = -1;
+  RAISE NOTICE '%', VARIABLE(plpgsql_sesvar02);
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT svartest_plpgsql_func02();
+
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+CREATE VARIABLE plpgsql_sesvar02 AS int[];
+
+SELECT svartest_plpgsql_func02();
+
+DROP FUNCTION svartest_plpgsql_func02();
+
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+
+-- returns updated value
+CREATE VARIABLE plpgsql_sesvar01 AS int;
+
+CREATE OR REPLACE FUNCTION svartest_plpgsql_inc(int)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sesvar01 = COALESCE(VARIABLE(plpgsql_sesvar01) + $1, $1);
+  RETURN VARIABLE(plpgsql_sesvar01);
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT svartest_plpgsql_inc(1);
+SELECT svartest_plpgsql_inc(1);
+SELECT svartest_plpgsql_inc(1);
+
+SELECT svartest_plpgsql_inc(1) FROM generate_series(1,10);
+
+CREATE VARIABLE plpgsql_sesvar02 AS numeric;
+
+LET plpgsql_sesvar02 = 0.0;
+
+CREATE OR REPLACE FUNCTION svartest_plpgsql_inc(numeric)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sesvar02 = COALESCE(VARIABLE(plpgsql_sesvar02) + $1, $1);
+  RETURN VARIABLE(plpgsql_sesvar02);
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT svartest_plpgsql_inc(1.0);
+SELECT svartest_plpgsql_inc(1.0);
+SELECT svartest_plpgsql_inc(1.0);
+
+SELECT svartest_plpgsql_inc(1.0) FROM generate_series(1,10);
+
+DROP VARIABLE plpgsql_sesvar01, plpgsql_sesvar02;
+
+DROP FUNCTION svartest_plpgsql_inc(int);
+DROP FUNCTION svartest_plpgsql_inc(numeric);
+
+-- the value should not be corrupted
+CREATE VARIABLE plpgsql_sesvar03 text;
+LET plpgsql_sesvar03 = 'abc';
+
+CREATE FUNCTION svartest_plpgsql_func03()
+RETURNS text AS $$
+BEGIN
+  RETURN svartest_plpgsql_func_nested(VARIABLE(plpgsql_sesvar03));
+END
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION svartest_plpgsql_func_nested(t text)
+RETURNS text AS $$
+BEGIN
+  LET plpgsql_sesvar03 = 'BOOM!';
+  RETURN t;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT svartest_plpgsql_func03();
+
+DROP FUNCTION svartest_plpgsql_func03();
+DROP FUNCTION svartest_plpgsql_func_nested(text);
+
+DROP VARIABLE plpgsql_sesvar03;
-- 
2.49.0