Re: Per-function GUC settings: trickier than it looked

Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>> And the rule becomes (I tend to forget things, so I like simple
>> rules that I can remember ;-) ) "For each SET-clause, there is
>> a pseudo-subtransaction affecting only *this* GUC".
>
> The other question is whether we want to change the behavior of SET
> LOCAL even in the absence of function SET-clauses. The current rule
> is that a LOCAL setting goes away at subtransaction commit, leading
> to this behavior:
>
> regression=# show regex_flavor;
> regex_flavor
> --------------
> advanced
> (1 row)
>
> regression=# begin;
> BEGIN
> regression=# savepoint x;
> SAVEPOINT
> regression=# set local regex_flavor to basic;
> SET
> regression=# release x;
> RELEASE
> regression=# show regex_flavor;
> regex_flavor
> --------------
> advanced
> (1 row)
>
> which makes some sense if you think of "release" as "subtransaction
> end", but not a lot if you think of it as forgetting a savepoint.
> Likewise, SET LOCAL within a plpgsql exception block goes away at
> successful block exit, which is not the first thing you'd expect.
> Neither of these behaviors are documented anywhere AFAIR; certainly
> the SET reference page doesn't explain 'em.
>
> I think we should probably take this opportunity to fix that, and
> make SET LOCAL mean "persists until end of current top-level
> transaction, unless rolled back earlier or within a function SET
> clause".
>
> So:
>
> * Plain SET takes effect immediately and persists unless rolled back
> or overridden by another explicit SET. In particular the value will
> escape out of a function that has a SET-clause for the same variable.
>
> * SET LOCAL takes effect immediately and persists until rolled back,
> overridden by another SET, or we exit a function that has a SET-clause
> for the same variable.
>
> * Rollback of a transaction or subtransaction cancels any SET or SET
> LOCAL within it. Otherwise, the latest un-rolled-back SET or SET LOCAL
> determines the active value within a transaction, and the latest
> un-rolled-back SET determines the value that will prevail after the
> transaction commits.
>
> * A function SET clause saves the entry-time value, and restores it at
> function exit, except when overridden by an un-rolled-back SET (but not
> SET LOCAL) within the function.
>
> Clear to everyone? Any objections?
That makes "SET LOCAL" completely equivalent to "SET", except
when used inside a function that has a corresponding SET-clause, right?
So I think *if* this is done, "SET LOCAL" should be renamed to
"SET FUNCTION". This would also prevent confusion, because everyone
who currently uses SET LOCAL will have to change his code anyway,
since the semantics change for every use-case apart from functions
with SET-clauses, which don't exist in 8.2.
Or am I overlooking something?

And renaming SET LOCAL also emphasized that point that we are taking
away functionality here - even if that functionality might not seem
very useful.

BTW, I *did* check the documentation before responding to Simon's original
mail, and I *did* read it as "SET LOCAL goes away a subtransaction end".
I figured that since there is no word on subtransactions in that part
of the documentation, transaction will apply equally to both toplevel
and subtransaction. It might very well be that I'm the only one who
read it that way, though ;-) And I must admin that I wasn't completely
sure, so I *did* try it out before I posted...

I'd strong prefer "SET LOCAL" to kept it's current semantics, only that "SET
LOCAL" changes will now be rolled back if the function has a matching
SET-clause. For multiple reasons:
.) It's useful to be able to temporarily change GUCs from a client, and
being able to reset them afterwards. Using a subtransaction for this
is maybe a bit wastefull, but at least it works.
.) In pl/pgsql, that fact that "SET LOCAL" goes away after the current
BEGIN/END block seems entirely logical.
.) It doesn't take away existing functionality

greetings, Florian Pflug