Re: [HACKERS] proposal: session server side variables

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.

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

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?

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.

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!

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.