Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> 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;
regression=# savepoint x;
regression=# set local regex_flavor to basic;
regression=# release x;
regression=# show regex_flavor;
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
* 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
* 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
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend