Re: proposal: session server side variables

On 4 Jan. 2017 19:03, "Fabien COELHO" <coelho(at)cri(dot)ensmp(dot)fr> wrote:

>>> I respect your opinion and don't agree with it.
>>
>>
>> Yeah. I'm pretty overwhelmingly unconvinced too.
>
> I'm lost.
>
> The security-related use-case you have presented stores the status of the verification in a variable. If the variable is untransactional, then it has been shown that the variable status > may say ok while the verification has really really failed.

That's only a concern if the setting xact performs writes. If it's a
read-only lookup, all it has to do is set the variable last.

I agree that transactional variables whose value assignments come into
effect on commit would be useful. Like we have for NOTIFY. I do not
agree that they are _necessary_ such that a feature is not useful
without them. Nor do I agree that they are necessary for security
related use.

> Morover, there is no special cost in implementing transactional on session variables, has it is already done by pg. It can probably be reused.

Um, what? No, not at all.

GUCs are scoped, but not transactional, in the sense that a SET LOCAL
_overrides_ the outer SET for the lifetime of the xact or until
overwritten by some later SET LOCAL. On xact end, whether rollback or
commit, we just unwind the whole scope by popping a stack. Additional
handling is present for subxact and special scopes like functions with
SET.

Transactional assignments would instead need some kind of
(sub)transaction stack that flattens onto the outer layer on commit or
is popped and discarded on rollback. Not overwhelmingly expensive or
hard, but not something we have already.

We'd _definitely_ need to be able to declare such variables, so we
could specify their ON COMMIT behaviour (which GUCs don't have) and
define their scope (like we do for GUCs).

> An alternative is to implement sub (nested) transactions, like Oracle and MS SQL Server... but that would be quite some work.

What? We have those already, see SAVEPOINT and ROLLBACK TO SAVEPOINT.

Unless you mean autonomous transactions, which are not really nested,
they're closer to having the outer xact suspend while another xact
works, then resuming the outer xact.

--
Fabien.