Re: proposal: session server side variables

Hello Pavel,

On Sat, 24 Dec 2016, Pavel Stehule wrote:

Maybe you could consider removing the part of the message that you are not
responding to, so that it would be easier for the reader to see your
answers and comments.

>> Hmmm. So I understand that you would like to do something like:
>>
>> - call a secure function which sets a session variable with restricted
>> permissions
>> - do some things which cannot access or change the variable
>> - call another secure function which can access, update, remove the
>> variable...

I'm still not clear with your use case. Did I read you correctly? ISTM
that the above use case could be managed with insert/update/delete in a
table with chosen permissions from the functions...

>> Yep, but if you need persistant and transactional then probably you can
>> accept less performant...
>
> When you accept less performance, then you can use temporary tables. You
> can easy wrap it by few polymorphic functions.

Probably. This is probably true as well from what I understood from your
use case.

>> The namespace issue is unclear to me. Would a variable name clash with a
>> table name? It should if you want to be able write "SELECT stuff FROM
>> variablename", which may or may not be a good idea.
>
> It is based on history and experience - one fundamental issue of languages
> for stored procedures is a conflict of variables and SQL identifiers.

I agree that this is a pain, which could be solved by using a prefix, say
$<name> for instance.

> When variables are based on pg_class, there are not possibility to any
> new conflict.

If variables are based on pg_class, ISTM that they will cost anyway.

To sum up my current opinion, taking into accounts your use case and Tom &
Jim argments about performance, I think that variables should be either:

- full-featured database objects well integrated in the database logic:
CREATE/ALTER/DROP, in pg_class, subject to standard permissions,
constraints, transactions, possibly persistent... Basically like a one-row
table, although the implementation should be more efficient, I agree.

** OR **

- very light-weight, a simple server process key-value store, which would
not use CREATE/ALTER/DROP which suggest otherwise, they would not be
subject to permissions nor transactions nor persistence but die with the
session, goodbye. A possible concession to permissions would be to have a
per-role store, and/or some visibility/accessibility declaration at
creation time, but certainly not GRANT/RESTORE syntax which suggest a
database persistent object.

I'm very reserved about anything in between these two options, which looks
like a database object but is not really one, so I think that it create
confusion.

In both case, the syntax should be nice and elegant... i.e. not only based
on functions, probably it should use some prefix convention (@, $)...
For the light weight option.

DECLARE @someday DATE [ = <SQL EXPRESSION> ] [visibility restriction?];
... then use @now as a possible value anywhere, which will be
substituted quite early in the execution process, before planning.
-- update a variable value:
[SET, ASSIGN, ... nothing?] @someday = <SQL EXPRESSION>;

Ok, that is basically more or less the mysql syntax, too bad, but I think
it makes sense for a lightweight object which should not look like a
database object at all to avoid confusion.

As far as implementation is concerned, I would use a TEXT to TEXT hash
table, and implicit cast the result when substituting.

@var -> 'text value of var'::type_it_was_declared_with

--
Fabien.