Re: proposal: session server side variables

Hello Pavel,

> you are talk about light session variables like MSSQL or MySQL (with same
> syntax), I am talking about secure session variables like Oracle package
> variables (with similar access syntax).

Hmmm. I do not know this Oracle stuff... After looking at the online
documentation, my understanding of "Oracle package variables" refers to
full fledged database objects, in particular they are not session
limited. The example I found is about a variable holding the total number
of employees, with functions hire & fire (well, they call it remove_emp)
to update them when inserting or deleting an employee.

AFAICS they are shared between backends, subjects to transactions and
permissions, constraints and so on. So they look more like the first
category I outlined, and probably they cost as any persistent database
object, which make sense. They constitute a consistent design.

This is * not * what you are proposing.

> [...] I have two important reasons why I insist on pg_class base.
>
> 1. security .. it is really fundamental part
> 2. possibility to static check by plpgsql_check - without entry in pg_class
> (or other catalogue table) I have not any valid information about type,
> existence of any variable.

Hmmm. I'm not quite convinced that putting session variables in pg_class
is a good idea, because it adds significant costs for the use case of
"standard" simple session variables, which is quite more probable than
session-but-with-permissions variables.

As far as security is concerned, ISTM that a limited but still useful
access control can be implemented for a key-value store with simple
session variables, see below.

As far as typing is concerned, ISTM that it can be done as well for
session variables by going through text and using casts when setting and
getting values, or through some other simple ad-hoc checking.

> Although I am not supporter (due possible issues with plpgsql_checks) of
> MySQL or MSSQL style variables I am not strongly against this
> implementation with same syntax. But it is different feature, with
> different benefits and costs.

> I didn't proposed the packages (and package variables) due issues in
> multilingual PostgreSQL environment and because it is redundant to
> PostgreSQL schemas.

> Instead I proposed >>secure global session variables<< (global like
> global temporary tables).

That's where I'm leaving you and start disagreeing, because it is not
consistent: you are proposing session variables that do not look like
session variable and are somehow costly.

I could agree with real "secure global variables" as in Oracle packages, a
consistent kind of database object which stores a persistent value safely
and securely. That would cost, but that is life in a database, you have
great things for a price. Probably that could be implemented as a row in
some special table, or as a one-row table, or whatever.

I could also agree with à la MS or MY-SQL session variables that look like
session variables, with limited ambition, light-weight and inexpensive.

I disagree with having a half-backed stuff, where something looks like a
database object (i.e. CREATE/ALTER/DROP/GRANT/REVOKE) but is really a
session object with strange properties. I also disagree to the pg_class
approach as it creates in effect an expensive session object while a
simple session object would cost much less and would be much more useful.

To summarize, I still think that your design is not consistent, even if it
makes sense for some degree wrt the implementation.

A possible compromise I have proposed is to have some declared access
restrictions on simple session variables, so that say only the owner can
access it, but they should stay and look like light-weight session
variables nevertheless. That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
SET @secure_variable = 3;

SET ROLE BasicUser;
SELECT @secure_variable; -- say NULL or error does not exist...

SET ROLE Admin;
SELECT @secure_variable; 3
...

> Currently light session variables can be implemented as not big
> extension.

Sure. I would review that as well.

--
Fabien.