Re: proposal: session server side variables

Hello,

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

Sure. However I do not see the point of proposing a feature which works
only sometimes, on the condition that the security setup does NOT involve
storing data in the database (!), otherwise it may be insecure in some
cases, sorry mates.

That does not look too serious, esp if the use-case concern is all about
security.

> If it's a read-only lookup, all it has to do is set the variable last.

Yep, I guess it would probably work for read-only transactions.

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

The feature would be clearly misleading without transactional support,
because people would use it with false expectation that it works securely,
which is not the case.

>> 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, [...]

The documentation is very scarse, so I have tested it.

All tests I have done with commit & rollback on session variables (SET
SESSION) have shown a clean transactional behavior, with the value
reverted on ROLLBACK, whether intentional or automatic, or the new value
set on COMMIT. See attached scripts for instance.

LOCAL variables are a different thing, they just disappear at the end of
the session, it is more a scoping thing.

> We'd _definitely_ need to be able to declare such variables, so we
> could specify their ON COMMIT behaviour (which GUCs don't have)

Hmmm. We do not have to declare any ON COMMIT behaviour of TABLES, they
are just transactional.

> and define their scope (like we do for GUCs).

I'm fine with defining scopes.

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

No, that is not what I meant.

> Unless you mean autonomous transactions, which are not really nested,

Yes, that is why I wrote "nested" above.

> they're closer to having the outer xact suspend while another xact
> works, then resuming the outer xact.

Yep. The point is that you can test the success of the nested transaction
before setting the status.

--
Fabien.