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

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> Tom Lane wrote:
>> 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?

Maybe it wasn't clear :-(. They aren't equivalent because in the
absence of rollback, SET's effects persist past main-transaction end;
SET LOCAL's don't. That's the way they were defined originally
(pre-subtransactions) and it still seems to make sense.

> 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.

I'm not sure how many people have really written code that depends on
the behavior of SET LOCAL rolling back at successful subtransaction end.
I think we'd have heard about it if very many people had noticed,
because it's not what the manual says.

For the one use we've actually advocated (setting a temporary value
within a function and then reverting to the old setting before exit),
there isn't any visible change in behavior, since abandonment of the
restored value at subtransaction end still ends up with the same result.

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

We can't break the officially advocated solution for secure search_path.
However, that particular coding pattern will still work with the change
I'm proposing. It's only where you *don't* manually restore the prior
value that you might notice a difference.

> 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.

Yeah, but you know that it's subtransactions under the hood, whereas
someone who's thinking in terms of SAVEPOINT/RELEASE and BEGIN/EXCEPTION
probably hasn't a clue about that.

> .) In pl/pgsql, that fact that "SET LOCAL" goes away after the current
> BEGIN/END block seems entirely logical.

I don't think so ... your other side-effects such as table updates don't
disappear, so why should SET's?

I'm not necessarily averse to inventing a third version of SET, but I
don't see a well-thought-out proposal here. In particular, we should be
making an effort to *not* expose the concept of subtransaction at the
SQL level at all, because that's not what the spec has.

regards, tom lane