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.
I can see occasional uses for what you describe though. If we landed up with an xact scope option like we have for SET LOCAL GUCs, 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.
In my proposal was support for transaction scope - ON COMMIT RESET clause should be ok
Regards
Pavel
I guess defaulting to rolling back variable effects on xact rollback would be ok too. Just kind of limiting.