Op 19-08-2022 om 17:29 schreef Pavel Stehule:
pá 19. 8. 2022 v 15:57 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:
Hi
I am sending fresh update
- enhanced work with composite types - now the used composite type can be
enhanced, reduced and stored value is converted to expected format
- enhancing find_composite_type_dependencies to support session variables,
so the type of any field of used composite type cannot be changed
update - fix cpp check
v20220819-2-0001-Catalogue-support-for-session-variables.patch
v20220819-2-0002-session-variables.patch
v20220819-2-0003-typecheck-check-of-consistency-of-format-of-stored-v.patch
v20220819-2-0004-LET-command.patch
v20220819-2-0005-Support-of-LET-command-in-PLpgSQL.patch
v20220819-2-0006-DISCARD-VARIABLES-command.patch
v20220819-2-0007-Enhancing-psql-for-session-variables.patch
v20220819-2-0008-Possibility-to-dump-session-variables-by-pg_dump.patch
v20220819-2-0009-typedefs.patch
v20220819-2-0010-Regress-tests-for-session-variables.patch
v20220819-2-0011-fix.patch
v20220819-2-0012-This-patch-changes-error-message-column-doesn-t-exis.patch
v20220819-2-0013-documentation.patch
make check fails as a result of the errors in the attached
session_variables.out.
Erik
Regards
Pavel
CREATE SCHEMA svartest;
SET search_path = svartest;
CREATE VARIABLE var1 AS integer;
CREATE TEMP VARIABLE var2 AS text;
DROP VARIABLE var1, var2;
-- functional interface
CREATE VARIABLE var1 AS numeric;
CREATE ROLE var_test_role;
GRANT USAGE ON SCHEMA svartest TO var_test_role;
SET ROLE TO var_test_role;
-- should fail
SELECT var1;
ERROR: permission denied for session variable var1
SET ROLE TO DEFAULT;
GRANT READ ON VARIABLE var1 TO var_test_role;
SET ROLE TO var_test_role;
-- should fail
LET var1 = 10;
ERROR: permission denied for session variable var1
-- should work
SELECT var1;
var1
------
(1 row)
SET ROLE TO DEFAULT;
GRANT WRITE ON VARIABLE var1 TO var_test_role;
SET ROLE TO var_test_role;
-- should work
LET var1 = 333;
SET ROLE TO DEFAULT;
REVOKE ALL ON VARIABLE var1 FROM var_test_role;
CREATE OR REPLACE FUNCTION secure_var()
RETURNS int AS $$
SELECT svartest.var1::int;
$$ LANGUAGE sql SECURITY DEFINER;
SELECT secure_var();
secure_var
------------
333
(1 row)
SET ROLE TO var_test_role;
-- should fail
SELECT svartest.var1;
ERROR: permission denied for session variable var1
-- should work;
SELECT secure_var();
secure_var
------------
333
(1 row)
SET ROLE TO DEFAULT;
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v
= var1;
QUERY PLAN
-----------------------------------------------
Function Scan on pg_catalog.generate_series g
Output: v
Function Call: generate_series(1, 100)
Filter: ((g.v)::numeric = var1)
(4 rows)
CREATE VIEW schema_var_view AS SELECT var1;
SELECT * FROM schema_var_view;
var1
------
333
(1 row)
\c -
SET search_path = svartest;
-- should work still, but var will be empty
SELECT * FROM schema_var_view;
var1
------
(1 row)
LET var1 = pi();
SELECT var1;
var1
------------------
3.14159265358979
(1 row)
-- we can see execution plan of LET statement
EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
QUERY PLAN
----------------------------
SET SESSION VARIABLE
Result
Output: 3.14159265358979
(3 rows)
SELECT var1;
var1
------------------
3.14159265358979
(1 row)
CREATE VARIABLE var3 AS int;
CREATE OR REPLACE FUNCTION inc(int)
RETURNS int AS $$
BEGIN
LET svartest.var3 = COALESCE(svartest.var3 + $1, $1);
RETURN var3;
END;
$$ LANGUAGE plpgsql;
SELECT inc(1);
inc
-----
1
(1 row)
SELECT inc(1);
inc
-----
2
(1 row)
SELECT inc(1);
inc
-----
3
(1 row)
SELECT inc(1) FROM generate_series(1,10);
inc
-----
4
5
6
7
8
9
10
11
12
13
(10 rows)
SET ROLE TO var_test_role;
-- should fail
LET var3 = 0;
ERROR: permission denied for session variable var3
SET ROLE TO DEFAULT;
DROP VIEW schema_var_view;
DROP VARIABLE var1 CASCADE;
DROP VARIABLE var3 CASCADE;
-- composite variables
CREATE TYPE sv_xyz AS (x int, y int, z numeric(10,2));
CREATE VARIABLE v1 AS sv_xyz;
CREATE VARIABLE v2 AS sv_xyz;
\d v1
\d v2
LET v1 = (1,2,3.14);
LET v2 = (10,20,3.14*10);
-- should work too - there are prepared casts
LET v1 = (1,2,3.14);
SELECT v1;
v1
------------
(1,2,3.14)
(1 row)
SELECT v2;
v2
---------------
(10,20,31.40)
(1 row)
SELECT (v1).*;
x | y | z
---+---+------
1 | 2 | 3.14
(1 row)
SELECT (v2).*;
x | y | z
----+----+-------
10 | 20 | 31.40
(1 row)
SELECT v1.x + v1.z;
?column?
----------
4.14
(1 row)
SELECT v2.x + v2.z;
?column?
----------
41.40
(1 row)
-- access to composite fields should be safe too
-- should fail
SET ROLE TO var_test_role;
SELECT v2.x;
ERROR: permission denied for session variable v2
SET ROLE TO DEFAULT;
DROP VARIABLE v1;
DROP VARIABLE v2;
REVOKE USAGE ON SCHEMA svartest FROM var_test_role;
DROP ROLE var_test_role;
-- scalar variables should not be in conflict with qualified column
CREATE VARIABLE varx AS text;
SELECT varx.relname FROM pg_class varx WHERE varx.relname = 'pg_class';
relname
----------
pg_class
(1 row)
-- should fail
SELECT varx.xxx;
ERROR: type text is not composite
-- variables can be updated under RO transaction
BEGIN;
SET TRANSACTION READ ONLY;
LET varx = 'hello';
COMMIT;
SELECT varx;
varx
-------
hello
(1 row)
DROP VARIABLE varx;
CREATE TYPE t1 AS (a int, b numeric, c text);
CREATE VARIABLE v1 AS t1;
LET v1 = (1, pi(), 'hello');
SELECT v1;
v1
----------------------------
(1,3.14159265358979,hello)
(1 row)
LET v1.b = 10.2222;
SELECT v1;
v1
-------------------
(1,10.2222,hello)
(1 row)
-- should fail
LET v1.x = 10;
ERROR: cannot assign to field "x" of column or variable "v1" because there is
no such column in data type t1
LINE 1: LET v1.x = 10;
^
DROP VARIABLE v1;
DROP TYPE t1;
-- arrays are supported
CREATE VARIABLE va1 AS numeric[];
LET va1 = ARRAY[1.1,2.1];
LET va1[1] = 10.1;
SELECT va1;
va1
------------
{10.1,2.1}
(1 row)
CREATE TYPE ta2 AS (a numeric, b numeric[]);
CREATE VARIABLE va2 AS ta2;
LET va2 = (10.1, ARRAY[0.0, 0.0]);
LET va2.a = 10.2;
SELECT va2;
va2
--------------------
(10.2,"{0.0,0.0}")
(1 row)
LET va2.b[1] = 10.3;
SELECT va2;
va2
---------------------
(10.2,"{10.3,0.0}")
(1 row)
DROP VARIABLE va1;
DROP VARIABLE va2;
DROP TYPE ta2;
-- default values
CREATE VARIABLE v1 AS numeric DEFAULT pi();
LET v1 = v1 * 2;
SELECT v1;
v1
------------------
6.28318530717958
(1 row)
CREATE TYPE t2 AS (a numeric, b text);
CREATE VARIABLE v2 AS t2 DEFAULT (NULL, 'Hello');
LET svartest.v2.a = pi();
SELECT v2;
v2
--------------------------
(3.14159265358979,Hello)
(1 row)
-- should fail due dependency
DROP TYPE t2;
ERROR: cannot drop type t2 because other objects depend on it
DETAIL: session variable v2 depends on type t2
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- should be ok
DROP VARIABLE v1;
DROP VARIABLE v2;
-- tests of alters
CREATE SCHEMA var_schema1;
CREATE SCHEMA var_schema2;
CREATE VARIABLE var_schema1.var1 AS integer;
LET var_schema1.var1 = 1000;
SELECT var_schema1.var1;
var1
------
1000
(1 row)
ALTER VARIABLE var_schema1.var1 SET SCHEMA var_schema2;
SELECT var_schema2.var1;
var1
------
1000
(1 row)
CREATE ROLE var_test_role;
ALTER VARIABLE var_schema2.var1 OWNER TO var_test_role;
SET ROLE TO var_test_role;
-- should fail, no access to schema var_schema2.var
SELECT var_schema2.var1;
ERROR: permission denied for schema var_schema2
DROP VARIABLE var_schema2.var1;
ERROR: permission denied for schema var_schema2
SET ROLE TO DEFAULT;
ALTER VARIABLE var_schema2.var1 SET SCHEMA public;
SET ROLE TO var_test_role;
SELECT public.var1;
var1
------
1000
(1 row)
ALTER VARIABLE public.var1 RENAME TO var1_renamed;
SELECT public.var1_renamed;
var1_renamed
--------------
1000
(1 row)
DROP VARIABLE public.var1_renamed;
SET ROLE TO DEFAULt;
-- default rights test
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON VARIABLES TO
var_test_role;
CREATE VARIABLE public.var2 AS int;
SET ROLE TO var_test_role;
-- should be ok
LET public.var2 = 100;
SELECT public.var2;
var2
------
100
(1 row)
SET ROLE TO DEFAULt;
DROP VARIABLE public.var2;
DROP OWNED BY var_test_role;
DROP ROLE var_test_role;
CREATE VARIABLE xx AS text DEFAULT 'hello';
SELECT xx, upper(xx);
xx | upper
-------+-------
hello | HELLO
(1 row)
LET xx = 'Hi';
SELECT xx;
xx
----
Hi
(1 row)
DROP VARIABLE xx;
-- ON TRANSACTION END RESET tests
CREATE VARIABLE t1 AS int DEFAULT -1 ON TRANSACTION END RESET;
BEGIN;
SELECT t1;
t1
----
-1
(1 row)
LET t1 = 100;
SELECT t1;
t1
-----
100
(1 row)
COMMIT;
SELECT t1;
t1
----
-1
(1 row)
BEGIN;
SELECT t1;
t1
----
-1
(1 row)
LET t1 = 100;
SELECT t1;
t1
-----
100
(1 row)
ROLLBACK;
SELECT t1;
t1
----
-1
(1 row)
DROP VARIABLE t1;
CREATE VARIABLE v1 AS int DEFAULT 0;
CREATE VARIABLE v2 AS text DEFAULT 'none';
LET v1 = 100;
LET v2 = 'Hello';
SELECT v1, v2;
v1 | v2
-----+-------
100 | Hello
(1 row)
LET v1 = DEFAULT;
LET v2 = DEFAULT;
SELECT v1, v2;
v1 | v2
----+------
0 | none
(1 row)
DROP VARIABLE v1;
DROP VARIABLE v2;
-- ON COMMIT DROP tests
-- should be 0 always
SELECT count(*) FROM pg_variable;
count
-------
0
(1 row)
CREATE TEMP VARIABLE g AS int ON COMMIT DROP;
SELECT count(*) FROM pg_variable;
count
-------
0
(1 row)
BEGIN;
CREATE TEMP VARIABLE g AS int ON COMMIT DROP;
COMMIT;
SELECT count(*) FROM pg_variable;
count
-------
0
(1 row)
BEGIN;
CREATE TEMP VARIABLE g AS int ON COMMIT DROP;
ROLLBACK;
SELECT count(*) FROM pg_variable;
count
-------
0
(1 row)
-- test on query with workers
CREATE TABLE svar_test(a int);
INSERT INTO svar_test SELECT * FROM generate_series(1,1000000);
ANALYZE svar_test;
CREATE VARIABLE zero int;
LET zero = 0;
-- parallel workers should be used
EXPLAIN (costs off) SELECT count(*) FROM svar_test WHERE a%10 = zero;
QUERY PLAN
--------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on svar_test
Filter: ((a % 10) = zero)
(6 rows)
-- result should be 100000
SELECT count(*) FROM svar_test WHERE a%10 = zero;
count
--------
100000
(1 row)
LET zero = (SELECT count(*) FROM svar_test);
-- result should be 1000000
SELECT zero;
zero
---------
1000000
(1 row)
-- parallel workers should be used
EXPLAIN (costs off) LET zero = (SELECT count(*) FROM svar_test);
QUERY PLAN
----------------------------------------------------------
SET SESSION VARIABLE
Result
InitPlan 1 (returns $1)
-> Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on svar_test
(8 rows)
DROP TABLE svar_test;
DROP VARIABLE zero;
-- use variables in prepared statements
CREATE VARIABLE v AS numeric;
LET v = 3.14;
-- use variables in views
CREATE VIEW vv AS SELECT COALESCE(v, 0) + 1000 AS result;
SELECT * FROM vv;
result
---------
1003.14
(1 row)
-- start a new session
\c
SET search_path to svartest;
SELECT * FROM vv;
result
--------
1000
(1 row)
LET v = 3.14;
SELECT * FROM vv;
result
---------
1003.14
(1 row)
-- should fail, dependency
DROP VARIABLE v;
ERROR: cannot drop session variable v because other objects depend on it
DETAIL: view vv depends on session variable v
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- should be ok
DROP VARIABLE v CASCADE;
NOTICE: drop cascades to view vv
-- other features
CREATE VARIABLE dt AS integer DEFAULT 0;
LET dt = 100;
SELECT dt;
dt
-----
100
(1 row)
DISCARD VARIABLES;
SELECT dt;
dt
----
0
(1 row)
DROP VARIABLE dt;
-- NOT NULL
CREATE VARIABLE v1 AS int NOT NULL;
CREATE VARIABLE v2 AS int NOT NULL DEFAULT NULL;
-- should fail
SELECT v1;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v1"
DETAIL: The session variable was not initialized yet.
SELECT v2;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v2"
LET v1 = NULL;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v1"
LET v2 = NULL;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v2"
LET v1 = DEFAULT;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v1"
LET v2 = DEFAULT;
ERROR: null value is not allowed for NOT NULL session variable "svartest.v2"
-- should be ok
LET v1 = 100;
LET v2 = 1000;
SELECT v1, v2;
v1 | v2
-----+------
100 | 1000
(1 row)
DROP VARIABLE v1;
DROP VARIABLE v2;
CREATE VARIABLE tv AS int;
CREATE VARIABLE IF NOT EXISTS tv AS int;
NOTICE: session variable "tv" already exists, skipping
DROP VARIABLE tv;
CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100;
SELECT iv;
iv
-----
100
(1 row)
-- should fail;
LET iv = 10000;
ERROR: session variable "svartest.iv" is declared IMMUTABLE
DROP VARIABLE iv;
-- different order
CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100;
-- should to fail
LET iv = 10000;
ERROR: session variable "svartest.iv" is declared IMMUTABLE
-- should be ok
SELECT iv;
iv
-----
100
(1 row)
DROP VARIABLE iv;
CREATE IMMUTABLE VARIABLE iv AS int;
-- should be ok
LET iv = NULL;
-- should fail
LET iv = NULL;
ERROR: session variable "svartest.iv" is declared IMMUTABLE
DROP VARIABLE iv;
-- create variable inside plpgsql block
DO $$
BEGIN
CREATE VARIABLE do_test_svar AS date DEFAULT '2000-01-01';
END;
$$;
SELECT do_test_svar;
do_test_svar
--------------
01-01-2000
(1 row)
DROP VARIABLE do_test_svar;
-- should fail
CREATE IMMUTABLE VARIABLE xx AS int NOT NULL;
ERROR: IMMUTABLE NOT NULL variable requires default expression
-- REASSIGN OWNED test
CREATE ROLE var_test_role1;
CREATE ROLE var_test_role2;
CREATE VARIABLE xxx_var AS int;
ALTER VARIABLE xxx_var OWNER TO var_test_role1;
REASSIGN OWNED BY var_test_role1 to var_test_role2;
SELECT varowner::regrole FROM pg_variable WHERE varname = 'xxx_var';
varowner
----------------
var_test_role2
(1 row)
DROP OWNED BY var_test_role1;
DROP ROLE var_test_role1;
SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var';
count
-------
1
(1 row)
DROP OWNED BY var_test_role2;
DROP ROLE var_test_role2;
SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var';
count
-------
0
(1 row)
-- creating, dropping temporary variable
BEGIN;
CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP;
LET tempvar = 100;
SAVEPOINT s1;
DROP VARIABLE tempvar;
ROLLBACK TO s1;
SELECT tempvar;
tempvar
---------
100
(1 row)
COMMIT;
-- should to fail
LET tempvar = 100;
ERROR: session variable "tempvar" doesn't exist
LINE 1: LET tempvar = 100;
^
BEGIN;
SAVEPOINT s1;
CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP;
LET tempvar = 100;
ROLLBACK TO s1;
COMMIT;
-- should to fail
LET tempvar = 100;
ERROR: session variable "tempvar" doesn't exist
LINE 1: LET tempvar = 100;
^
CREATE VARIABLE var1 AS int;
LET var1 = 100;
BEGIN;
DROP VARIABLE var1;
ROLLBACK;
SELECT var1;
var1
------
100
(1 row)
DROP VARIABLE var1;
CREATE VARIABLE var1 AS int DEFAULT 100;
COMMENT ON VARIABLE var1 IS 'some variable comment';
SELECT pg_catalog.obj_description(oid, 'pg_variable') FROM pg_variable WHERE
varname = 'var1';
obj_description
-----------------------
some variable comment
(1 row)
DROP VARIABLE var1;
CREATE TABLE xxtab(avar int);
CREATE TYPE xxtype AS (avar int);
CREATE VARIABLE xxtab AS xxtype;
INSERT INTO xxtab VALUES(10);
-- it is ambiguous, but columns are preferred
SELECT xxtab.avar FROM xxtab;
avar
------
10
(1 row)
SET session_variables_ambiguity_warning TO on;
SELECT xxtab.avar FROM xxtab;
WARNING: session variable "xxtab.avar" is shadowed
LINE 1: SELECT xxtab.avar FROM xxtab;
^
DETAIL: Session variables can be shadowed by columns, routine's variables and
routine's arguments with same name.
avar
------
10
(1 row)
SET search_path = svartest;
CREATE VARIABLE testvar as int;
-- plpgsql variables are preferred against session variables
DO $$
<<myblock>>
DECLARE testvar int;
BEGIN
-- should be ok without warning
LET testvar = 100;
-- should be ok without warning
testvar := 1000;
-- should be ok without warning
RAISE NOTICE 'session variable is %', svartest.testvar;
-- should be ok without warning
RAISE NOTICE 'plpgsql variable is %', myblock.testvar;
-- should to print plpgsql variable with warning
RAISE NOTICE 'variable is %', testvar;
END;
$$;
NOTICE: session variable is 100
NOTICE: plpgsql variable is 1000
WARNING: session variable "testvar" is shadowed
LINE 1: testvar
^
DETAIL: Session variables can be shadowed by columns, routine's variables and
routine's arguments with same name.
QUERY: testvar
NOTICE: variable is 1000
DROP VARIABLE testvar;
SET session_variables_ambiguity_warning TO default;
-- should be ok
SELECT avar FROM xxtab;
avar
------
10
(1 row)
CREATE VARIABLE public.avar AS int;
-- should to fail
SELECT avar FROM xxtab;
avar
------
10
(1 row)
-- should be ok
SELECT public.avar FROM xxtab;
avar
------
(1 row)
DROP VARIABLE xxtab;
SELECT xxtab.avar FROM xxtab;
avar
------
10
(1 row)
DROP VARIABLE public.avar;
DROP TYPE xxtype;
DROP TABLE xxtab;
-- test of plan cache invalidation
CREATE VARIABLE xx AS int;
SET plan_cache_mode = force_generic_plan;
PREPARE pp AS SELECT xx;
EXECUTE pp;
xx
----
(1 row)
DROP VARIABLE xx;
CREATE VARIABLE xx AS int;
-- should to work
EXECUTE pp;
xx
----
(1 row)
DROP VARIABLE xx;
DEALLOCATE pp;
SET plan_cache_mode = DEFAULT;
CREATE ROLE var_test_role;
CREATE SCHEMA vartest;
GRANT USAGE ON SCHEMA vartest TO var_test_role;
CREATE VARIABLE vartest.x AS int;
CREATE VARIABLE vartest.y AS int;
LET vartest.x = 100;
LET vartest.y = 101;
GRANT READ ON ALL VARIABLES IN SCHEMA vartest TO var_test_role;
SET ROLE TO var_test_role;
SELECT vartest.x, vartest.y;
x | y
-----+-----
100 | 101
(1 row)
SET ROLE TO DEFAULT;
REVOKE READ ON ALL VARIABLES IN SCHEMA vartest FROM var_test_role;
SET ROLE TO var_test_role;
-- should to fail
SELECT vartest.x;
ERROR: permission denied for session variable x
SELECT vartest.y;
ERROR: permission denied for session variable y
SET ROLE TO DEFAULT;
DROP VARIABLE vartest.x, vartest.y;
DROP SCHEMA vartest;
DROP ROLE var_test_role;
-- test cached plan
CREATE VARIABLE v1 AS text;
CREATE VARIABLE v2 AS int;
CREATE VARIABLE v3 AS int;
LET v1 = 'test';
LET v2 = 10;
LET v3 = 5;
PREPARE q1 AS SELECT v1 || i FROM generate_series(1, v2) g(i) WHERE i IN (v2,
v3);
SET plan_cache_mode to force_generic_plan;
EXECUTE q1;
?column?
----------
test5
test10
(2 rows)
EXPLAIN EXECUTE q1;
QUERY PLAN
-----------------------------------------------------------------------
Function Scan on generate_series g (cost=0.00..0.14 rows=2 width=32)
Filter: (i = ANY (ARRAY[v2, v3]))
(2 rows)
-- dependecy check
DROP VARIABLE v3;
-- recreate v3 again
CREATE VARIABLE v3 AS int DEFAULT 6;
-- should to work, the plan should be recreated
EXECUTE q1;
?column?
----------
test6
test10
(2 rows)
DEALLOCATE q1;
-- fill v1 by long text
LET v1 = repeat(' ', 10000);
PREPARE q1 AS SELECT length(v1);
EXECUTE q1;
length
--------
10000
(1 row)
LET v1 = repeat(' ', 5000);
EXECUTE q1;
length
--------
5000
(1 row)
DEALLOCATE q1;
SET plan_cache_mode to default;
DROP VARIABLE v1, v2, v3;
CREATE ROLE var_test_role;
CREATE VARIABLE public.v1 AS int DEFAULT 0;
-- check acl when variable is acessed by simple eval expr method
CREATE OR REPLACE FUNCTION public.fx_var(int)
RETURNS int AS $$
DECLARE xx int;
BEGIN
xx := public.v1 + $1;
RETURN xx;
END;
$$ LANGUAGE plpgsql;
-- should be ok
SELECT public.fx_var(0);
fx_var
--------
0
(1 row)
SET ROLE TO var_test_role;
-- should to fail
SELECT public.fx_var(0);
ERROR: permission denied for session variable v1
CONTEXT: PL/pgSQL function public.fx_var(integer) line 4 at assignment
SET ROLE TO default;
GRANT READ ON VARIABLE public.v1 TO var_test_role;
SET ROLE TO var_test_role;
-- should be ok
SELECT public.fx_var(0);
fx_var
--------
0
(1 row)
SET ROLE TO default;
REVOKE READ ON VARIABLE public.v1 FROM var_test_role;
SET ROLE TO var_test_role;
-- should be fail
SELECT public.fx_var(0);
ERROR: permission denied for session variable v1
CONTEXT: PL/pgSQL function public.fx_var(integer) line 4 at assignment
SET ROLE TO DEFAULT;
DROP FUNCTION public.fx_var(int);
DROP VARIABLE public.v1;
DROP ROLE var_test_role;
CREATE TYPE public.svar_test_type AS (a int, b int, c numeric);
CREATE VARIABLE public.svar AS public.svar_test_type;
LET public.svar = ROW(10,20,30);
SELECT public.svar;
svar
------------
(10,20,30)
(1 row)
ALTER TYPE public.svar_test_type DROP ATTRIBUTE c;
-- should to fail
SELECT public.svar;
svar
---------
(10,20)
(1 row)
ALTER TYPE public.svar_test_type ADD ATTRIBUTE c int;
-- should to fail too (different type, different generation number);
SELECT public.svar;
svar
----------
(10,20,)
(1 row)
LET public.svar = ROW(10,20,30);
-- should be ok again for new value
SELECT public.svar;
svar
------------
(10,20,30)
(1 row)
DROP VARIABLE public.svar;
DROP TYPE public.svar_test_type;
CREATE VARIABLE public.svar AS int;
SELECT schema, name, removed FROM pg_debug_show_used_session_variables();
schema | name | removed
--------+------+---------
(0 rows)
LET public.svar = 100;
SELECT schema, name, removed FROM pg_debug_show_used_session_variables();
schema | name | removed
--------+------+---------
public | svar | f
(1 row)
BEGIN;
DROP VARIABLE public.svar;
-- value should be in memory
SELECT schema, name, removed FROM pg_debug_show_used_session_variables();
schema | name | removed
--------+------+---------
public | svar | t
(1 row)
ROLLBACK;
-- value should be in memory
SELECT schema, name, removed FROM pg_debug_show_used_session_variables() WHERE
schema = 'public' and name = 'svar';
schema | name | removed
--------+------+---------
public | svar | f
(1 row)
SELECT public.svar;
svar
------
100
(1 row)
BEGIN;
DROP VARIABLE public.svar;
-- value should be in memory
SELECT schema, name, removed FROM pg_debug_show_used_session_variables() WHERE
schema = 'public' and name = 'svar';
schema | name | removed
--------+------+---------
public | svar | t
(1 row)
COMMIT;
-- the memory should be clean;
SELECT schema, name, removed FROM pg_debug_show_used_session_variables() WHERE
schema = 'public' and name = 'svar';
schema | name | removed
--------+------+---------
(0 rows)
BEGIN;
CREATE VARIABLE public.svar AS int;
LET public.svar = 100;
ROLLBACK;
-- the memory should be clean;
SELECT schema, name, removed FROM pg_debug_show_used_session_variables() WHERE
schema = 'public' and name = 'svar';
schema | name | removed
--------+------+---------
(0 rows)
CREATE VARIABLE public.svar AS int;
LET public.svar = 100;
-- repeated aborted transaction
BEGIN; DROP VARIABLE public.svar; ROLLBACK;
BEGIN; DROP VARIABLE public.svar; ROLLBACK;
BEGIN; DROP VARIABLE public.svar; ROLLBACK;
-- the value should be still available
SELECT public.svar;
svar
------
100
(1 row)
DROP VARIABLE public.svar;
CREATE TYPE public.svar_test_type AS (a int, b int);
CREATE VARIABLE public.svar AS public.svar_test_type;
SELECT public.svar;
svar
------
(1 row)
ALTER TYPE public.svar_test_type ADD ATTRIBUTE c int;
SELECT public.svar;
svar
------
(1 row)
ALTER TYPE public.svar_test_type DROP ATTRIBUTE b;
SELECT public.svar;
svar
------
(1 row)
DROP VARIABLE public.svar;
DROP TYPE public.svar_test_type;
CREATE TYPE public.svar_test_type AS (a int, b int);
CREATE VARIABLE public.svar AS public.svar_test_type;
CREATE VARIABLE public.svar2 AS public.svar_test_type;
LET public.svar = (10, 20);
ALTER TYPE public.svar_test_type ADD ATTRIBUTE c int;
SELECT public.svar;
svar
----------
(10,20,)
(1 row)
LET public.svar2 = (10, 20, 30);
ALTER TYPE public.svar_test_type DROP ATTRIBUTE b;
SELECT public.svar;
svar
-------
(10,)
(1 row)
SELECT public.svar2;
svar2
---------
(10,30)
(1 row)
DROP VARIABLE public.svar;
DROP VARIABLE public.svar2;
DROP TYPE public.svar_test_type;
-- The composite type cannot be changed when it is used
CREATE TYPE public.svar_type AS (a int, b int);
CREATE VARIABLE public.svar AS public.svar_type;
-- should to fail
ALTER TYPE public.svar_type ALTER ATTRIBUTE b TYPE numeric;
ERROR: cannot alter type "svar_type" because session variable "public.svar"
uses it
DROP VARIABLE public.svar;
CREATE TYPE public.svar_type2 AS (a int, b int, c public.svar_type);
CREATE VARIABLE public.svar AS public.svar_type2;
-- should to fail
ALTER TYPE public.svar_type ALTER ATTRIBUTE b TYPE numeric;
ERROR: cannot alter type "svar_type" because session variable "public.svar"
uses it
DROP VARIABLE public.svar;
DROP TYPE public.svar_type2;
DROP TYPE public.svar_type;
