Re: proposal: schema variables - Mailing list pgsql-hackers

Maybe the warning of usage of unfenced variables can be changed (enhanced) to some different mechanism that can be more restrictive (and safer), but I think it can still be user friendly.

My idea is based on assumption so users with knowledge of stored procedures know  variables and related risks (and know tools how to minimize risks), and for other people the risk is higher and we should force usage of variable fences. I think we can force usage of variable fences at query runtime, when query is not executed from the SPI environment. This behaviour can be enabled by default, but can be optionally disabled. 

CREATE VARIABLE s.x AS int; -- allowed when user has create right on schema s

CREATE VIEW v1 AS SELECT x; -- no problem, the check is dynamic (execution), not static

CREATE VIEW v2 AS SELECT VARIABLE(x); -- no problem

SELECT x; -- fails on access to unfenced variable

SELECT * FROM v1; -- fails on access to unfenced variable

SELECT * FROM v2; -- ok

but inside pl, this check will not be active, and then with default setting I can write an code like

LET var = 10; -- fencing is not allowed there, and there is not any collision

DO $$

BEGIN

  RAISE NOTICE 'var=%', var;

  RAISE NOTICE 'var=%', (SELECT * FROM v1); --is ok here too

END;

$$;

Outside PL the fencing can be required, inside PL the fencing can be optional. With this proposal we can limit the possible risk usage of unfenced variables only in PL context, and the behaviour can be very similar to PL/SQL or SQL/PSM. This check is only a runtime check, so it has no impact on any DDL statement. It doesn't change the syntax or behavior, so it can be implemented subsequently - it is just a safeguard against unwanted usage of variables in an environment, where users have no possibility to use variables already. I can imagine that this check "allow_unfenced_variables" can have three values (everywhere, pl, nowhere) and the default can be pl. The results of queries don't depend on the current setting of this check. For all values for all possible queries and situations, the result is the same (when it is finished). But sometimes, the check can break the execution - in similar meaning like access rights. All previous proposed warnings can be unchanged.

Comments, notes?

Regards

Pavel