Re: [HACKERS] proposal: session server side variables - Mailing list pgsql-hackers

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