Re: proposal: session server side variables

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

>
> Hello Pavel,
>
> The session variables should be:
>>
>
> I have often wished I had such a feature, psql client side :-variables are
> just awful raw text things.
>
> A few comments, mostly about the design:
>
> 1. persistent objects with temporal unshared typed content. The life of
>> content should be limited by session or by transaction. The content is
>> initialized to default (when it is defined) or to NULL when variable is
>> first accessed in variable' time scope (session, transaction).
>>
>> CREATE VARIABLE [schema.]variable type [DEFAULT default_value]
>> [TRANSACTION|SESION SCOPE]
>>
>
> I'm not sure of the order, and from a parser perspective it is nice to
> announce the type before the value.
>

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.
Session secure variables are some different than in MSSQL or MySQL - so I
would not to use same syntax.

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.

>
> Maybe a SQL-server like @-prefix would be nice, something like:
>
> CREATE VARIABLE @foo TEXT DEFAULT 'hello' SCOPE SESSION;
>
> DROP VARIABLE [schema.]variable
>>
>
> In the long term, What would be the possible scopes?
>
> TRANSACTION, SESSION, PERSISTANT ?
>
> Would some scopes orthogonal (eg SHARED between sessions for a USER in a
> DATABASE, SHARED at the cluster level?).
>

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized extensions
will be better.

> How to deal with namespace issues?
>
> 2. accessed with respecting access rights:
>>
>> GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
>> REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role
>>
>
> At least for transaction and session scopes it does not make sense that
> they would be accessible outside the session/transaction, so grant/revoke
> do not seem necessary?

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 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.

Next step can be support of SQL statements

With SQL support you can do

SELECT varname;
SELECT * FROM compositevarname;
UPDATE varname SET value TO xxx;
UPDATE compositevarname SET field TO xxx;

> Also a nicer way to reference them would be great, like SQL server.
>
> SELECT * FROM SomeTable WHERE name = @name;
>
> A function may be called behind the scene, I'm just arguing about the
> syntax here...
>
> Important question, what nice syntax to assign the result of a query to a
> variable? Maybe it could be:
>
> SET @name = query-returning-one-row; -- hmmm
> SET @name FROM query-returning-one-row; -- maybe better
>
> Or:
>
> ALTER VARIABLE @name WITH one-row-query;
>
> Special variables could allow to get the number of rows modified by the
> last option, like in PL/pgSQL but at the SQL level?
>
> 4. non transactional - the metadata are transactional, but the content is
>> not.
>>
>
> 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.

If you need transactional content - then you should to use tables.

> I think that if the implementation is based on some system table for
> storage, then you could get the transaction properties for free, and it
> seems more logical to do so:
>
> CREATE TEMPORARY TABLE pg_session_variables(name TEXT PRIMARY KEY, value
> TEXT, oidtype, ...);
>
> CREATE VARIABLE @foo INTEGER; -- INSERT INTO TABLE ...
>
> SELECT * FROM x WHERE name = @foo;
> -- SELECT * FROM x WHERE name = (SELECT value::INT FROM
> pg_session_variables WHERE name='foo')
>
> So maybe some simple syntactic rewriting would be enough? Or some SPI
> function?

>
>
> --
> Fabien.
>