What if setup_user() succeeds as a function but the transaction it belongs to fails for some reason (eg deferred constraints, other operation related to setting user up but outside of this function fails, there is replication issue... whatever, a transaction may fail by definition)?
ISTM that the security models requires that USER_IS_AUDITOR is reverted, so although it is definitely a session variable, it must be transactional (MVCC) nevertheless.
No strong opinion here.
IMO the simplest answer should be the main focus here: if it's session level, it's session level. Not kinda-sesion-level kinda-transaction-level.
There is no contradiction between session level & transactions: a session executes transactions, fine. TEMP tables are MVCC *and* session level.
I can see occasional uses for what you describe though.
My question is not strictly about use, it is about a key security point related to the presented use case, which is about security. The whole discussion of the thread being about somehow-secured session variables.
ISTM that if the transaction setting the value fails and the secure variable says that all is well thus allows other operations to proceed believing that the credentials have been veted while in reality they have not, that's no good.
So my understanding of this use case is that the involved session variable which hold the state must be transactional. Other use cases may have different requirements and security implications.
If we landed up with an xact scope option like we have for SET LOCAL GUCs,
ISTM that it is a little different. The GUC local option makes the variable value always disappear after the xacts, whether it succeeds or not. The semantics needed here is that the value must disappear if the xact fails but not if it succeeds, which is the current behavior of GUCs with is_local=FALSE.
the option to mark it ON COMMIT RESET or ON COMMIT SET would be useful I guess. I'm not sure if it's worth the complexity.
My question right now is rather to determine what are the precise and hard requirements of the use case.
I guess defaulting to rolling back variable effects on xact rollback would be ok too. Just kind of limiting.
Yep, the variable value must be rolled back, I think.
attention! rollback is significantly expensive than RESET.
There are no any product where variables are transactional - we should not to create wheel.
