Re: proposal: session server side variables

Hello Pavel,

>> Hmmm. Switching role within a transaction. I never did need that... but
>> that is a use case.
>
> Any application with security definer functions - depends on different
> communities - it is used sometimes strongly.

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

> Probably we have different expectation from variables. I don't expect so
> variable can be changed by any rollback.

Indeed, it seems that we do not have the same expectations.

> What is use case for transactional variables? I miss any experience - I
> wrote lot plpgsql lines and newer would it.

Here are two use cases, which are neither good nor bad, but that I have in
mind when I'm argumenting.

(1) First use case I'm thinking of is software update, with persistent
transactional variables, eg:

-- let assume we have application_version = 1
BEGIN;
-- lock things up
-- update application schema and data to version 2
-- set application_version = 2
-- unlock things
COMMIT;

I would not want the application_version to remain at 2 if the COMMIT
fails, obviously. This is usually implemented with a one-row table, but
some kind of variable syntax could be quite elegant. For this use case, a
variable should be persistant, it does not it to be efficient, it should
have permissions and should be transactional.

(2) Second use case I'm thinking of is some kind of large batch
management.

-- variable batch_1_is_done = false
BEGIN;
-- try to do large batch 1...
-- set batch_1_is_done = true
COMMIT;
-- then test whether it worked, do some cleanup if not...
-- there are some discussions to get some \if in psql...

For this second example, I would not like batch_is_done to be true if the
commit failed, but I do not think that any permissions would be useful,
and it would be fine if it is just accessible from a session only.

> When I remove ACID, and allow only one value - then the implementation can
> be simple and fast - some next step can be support of expandable types.
> Sure - anybody can use temporary tables now and in future. But it is slow -
> more now, because we doesn't support global temporary tables. But ACID
> needs lot of CPU times, needs possible VACUUM, ...

Yep, but if you need persistant and transactional then probably you can
accept less performant...

> No ACID variables are simple to implement, simple to directly accessible
> from any PL (although I am thinking about better support in 2nd phase for
> PLpgSQL).

ACID may be simple to implement with some kind of underlying table, or
maybe a row in a table. How efficient it could be is another question, but
then if the feature does not allow some use cases, and it not so
interesting to have it. That is why I think that it is worth discussing
"silly" semantics and syntax.

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.

--
Fabien.