Re: [HACKERS] proposal: session server side variables

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.