Re: SET LOCAL <var> doesn't become undefined after transaction is commited

Kostya <kostya(dot)y(at)gmail(dot)com> writes:
> However, once I run the following transaction
> BEGIN READ WRITE
> SET LOCAL session.my_tenant_id="f00";
> SELECT * FROM someTable;
> COMMIT;

> Then
> SELECT current_setting("session.my_tenant_id");
> will return an empty string "". Basically "session.my_tenant_id" becomes
> defined.

> Is this supposed to work like this or could this be a bug?
> Is it possible to entirely undefine "session.my_tenant_id" after the
> transaction was committed? Basically I would like to return to the initial
> state of the PSQL connection.

It is supposed to work like that. There is no provision for rolling
back the existence of a GUC altogether, and if there were, it would
break the actually intended use-case, namely GUCs created by dynamically
loaded extensions. If an extension is loaded during a transaction,
it won't disappear if the transaction is rolled back, so neither
should its GUCs.

The real problem here is that you're abusing a feature that was never
meant to be used for user-defined variables. It does not have the
right behavior in corner cases (as you're seeing here), it does not
have the features you'd want (eg ability to declare the type of
a variable), and it definitely doesn't have the scalability to lots
of variables that I'd expect a user-focused feature to have.

There's a fairly long-running thread about creating a feature that
*is* meant for user variables:

https://www.postgresql.org/message-id/flat/CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ(at)mail(dot)gmail(dot)com

I haven't checked on the state of that lately, but you might read up
on it and help review/test it, or try to push the definition in the
direction you need if it doesn't seem like quite the right thing.

regards, tom lane