Re: proposal: session server side variables

2016-12-23 18:46 GMT+01:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello,
>
> I little bit dislike this style - in my proposal the session variables are
>> very near to a sequences - and we have not any special symbols for
>> sequences.
>>
>
> Yep, but we do not need a syntax to reference a sequence either... it is
> automatic and usually hidden behind SERIAL. I know there is a NEXTVAL
> function, I just never call it, so it is fine... If I define a variable I
> expect to have to use it.
>
> Session secure variables are some different than in MSSQL or MySQL - so I
>> would not to use same syntax.
>>
>
> I'm not sure why pg variables should be different from these other tools.
>

because MySQL variables are not declared - and allows assign everywhere -
and MSSQL variables are not persistent. Its total different creatures.

> What is the use case to cover? The few times I wished I had variables
> would have been covered by session-limited variables, for which
> grant/revoke do not make sense.
>
> I really would to use pg_class as base for metadata of variables -
>> conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..
>> With different syntax it all lost sense - and I'll to implement it again.
>>
>
> I also hate having my time going down the drain, but this cannot be the
> justification for a feature.
>
> I have a plan to support TRANSACTION and SESSION scope.
>>
>
> That looks ok to me.
>
> Persistent or shared scope needs much more complex rules, and some
>> specialized extensions will be better.
>>
>
> Or maybe they should be avoided altogether?
>
> [GRANT].
>> It is necessary - and I think so it is fundamental feature - any other
>> features can be more or less replaced by extensions, but this one cannot
>> or
>> not simply - you have to protect content against some users - some
>> cookies, ids have to be protected. It can be used well with RLS.
>> Ada language has packages, package variables. I would not to introduce
>> packages because are redundant to schemas, but I need some mechanism for
>> content protecting.
>>
>
> I do not understand why GRANT make sense. If a variable is set by a
> session/tx and only accessible to this session/tx, then only the client who
> put it can get it back, so it is more of a syntactic commodity?
>

In one session you can use lot of roles - some code can be used for
securing interactive work, some can be for securing some API, sometimes you
can secure a access to some sources. You can switch lot of roles by using
security definer functions.

>
> What appropriate use case would need more?
>
> I would not to introduce packages, because than I will have problem with
>> joining ADA packages with Perl, Python. Instead I introduce secure granted
>> access. More - I don't need to solve lexical scope - and I can use a wide
>> used mechanism.
>>
>
> 3. accessed/updated with special function "getvar", "setvar":
>>>
>>>>
>>>> FUNCTION getvar(regclass) RETURNS type
>>>> FUNCTION setvar(regclass, type) RETURNS void
>>>>
>>>
>>> From an aesthetical point of view, I do not like that much.
>>>
>>> If you use CREATE & DROP, then logically you should use ALTER:
>>>
>>> CREATE VARIABLE @name TEXT DEFAULT 'calvin';
>>> CREATE VARIABLE @name TEXT = 'calvin';
>>> ALTER VARIABLE @name SET VALUE TO 'hobbes';
>>> ALTER VARIABLE @name = 'hoobes';
>>> DROP VARIABLE @name;
>>>
>>> Maybe "SET" could be an option as well, but it is less logical:
>>>
>>> SET @name = 'susie';
>>>
>>> But then "SET @..." would just be a shortcut for ALTER VARIABLE.
>>>
>>
>> I would to use a SET statement too. But it is used for another target now.
>> Using ALTER in this content looks strange to me. It is used for changing
>> metadata not a value.
>>
>
> ALTER SEQUENCE does allow to change its value? Or maybe use UPDATE, as you
> suggest below...
>
> Next step can be support of SQL statements
>> With SQL support you can do
>>
>> SELECT varname;
>> UPDATE varname SET value TO xxx;
>>
>
> SELECT * FROM compositevarname;
>> UPDATE compositevarname SET field TO xxx;
>>
>
> I'm not at ease with the syntax because varname is both a value and a
> relation somehow... But maybe that make sense? Not sure, I'll think about
> it.
>
> Hmmm... Do you mean:
>>>
>>> CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
>>> BEGIN;
>>> SET @foo = 2;
>>> ROLLBACK;
>>>
>>> Then @foo is 2 despite the roolback? Yuk!
>>>
>>
>> This is similar to sequences.
>>
>
> That is not a good reason to do the same. Sequences are special objects
> for which the actual value is expected to be of no importance, only that it
> is different from the previous and the next. I do not think that
> "variables" should behave like that, because their value is important.
>
> If you need transactional content - then you should to use tables.
>>
>
> Why not.
>
> Maybe variables just need be a syntactic convenience around that?
>

There is pretty similar relation between sequences and tables and variables
and tables.

>
> A variable is a table with one row holding one value... In which case
> GRANT/REVOKE makes sense, because a table may be shared and persistent,
> thus is not limited to a session or a transaction.
>
> That allows to set constraints.
>

In first iteration the constraint can be implemented with domains - but
there is not any break to implement constraints directly on variables.

>
> CREATE VARIABLE foo INT NOT NULL DEFAULT 1 SCOPE SESSION/SESSION SCOPE;
> -> CREATE TEMPORARY TABLE foo(val INT NOT NULL DEFAULT 1) ONE ROW;
> -> INSERT INTO foo VALUES();
>
> @foo
> -> (SELECT val FROM foo LIMIT 1)
>
> @foo.field
> -> (SELECT field FROM foo LIMIT 1)
>
> SET @foo = 2;
> -> UPDATE @foo SET val = 2;
> SET @foo.field = 3;
> -> UPDATE foo SET field = 3;
>
> DROP VARIABLE foo;
> -> DROP TABLE foo;
>
> --
> Fabien.
>