Re: proposal: session server side variables

2016-12-24 12:19 GMT+01:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

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

On server side you can use PLpgSQL and handling exception.

On client side you can use technique used in MSSQL, where variables are not
transactional too.

BEGIN
-- servar state 1
statement;
-- servar state 2
statement;
COMMIT

We should to introduce client side session variable :STATUS

\if eq(:STATUS, 'ok')
...

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

When you accept less performance, then you can use temporary tables. You
can easy wrap it by few polymorphic functions.

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

It is based on history and experience - one fundamental issue of languages
for stored procedures is a conflict of variables and SQL identifiers. When
variables are based on pg_class, there are not possibility to any new
conflict.

More I can use a security related to schema - It is partial coverage of
package variables.

Regards

Pavel

>
> --
> Fabien.
>