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

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.

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?

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?

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.

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.