Re: [HACKERS] proposal: session server side variables - Mailing list pgsql-hackers

On 12/23/2016 08:20 AM, Pavel Stehule wrote:
> 2016-12-23 16:27 GMT+01:00 Fabien COELHO:
>> I have often wished I had such a feature, psql client side :-variables are
>> just awful raw text things.

Agreed.

>> 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 haven't looked, but I take it the SQL standard is silent on the issue
of variables?

> I really would to use pg_class as base for metadata of variables -
> conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..

That would be very useful.

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


I can see where persistent variables would be very useful though.


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

How would this work for transaction and session scopes though? What
would be the point -- no other access is possible other than what
happens in the session. Do you envision something like
CREATE VARIABLE foo ...;GRANT SELECT ON VARIABLE foo TO bob;SET ROLE bob;

?

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

Makes sense.

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

Maybe. Not sure I like that.

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

+1

> SELECT * FROM compositevarname;

+1

> UPDATE varname SET value TO xxx;
> UPDATE compositevarname SET field TO xxx;

These need more thought I think.

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

I think the SET syntax is growing on me, but I suspect there may be push
back on overloading that syntax.

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

Agreed

> This is similar to sequences.

I don't see how variables really have anything to do with sequences.

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

I definitely have use-cases where transactional variables would be useful.


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


I was thinking along those lines too.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development