Re: [HACKERS] 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.