Re: Per-function GUC settings: trickier than it looked - Mailing list pgsql-hackers

"Florian G. Pflug" <fgp@phlo.org> writes:
> Tom Lane wrote:
>> We could perhaps get away with defining that as being the behavior,
>> but it doubtless will surprise someone sometime.  What *should* these
>> interactions be like, and has anyone got an idea how to implement their
>> suggestion?

> What will happen if you have two functions, foo and bar, were the search-path
> is overridden for foo, and foo calls bar. I guess bar would be executed with
> foo's overridden searchpath. Thats seems a bit surprising -

I think it's correct; if bar doesn't SET a search_path then it should
use the caller's.

I thought a bit more about this and there are at least some cases we can
probably agree on without trouble:

* If a transaction or subtransaction aborts, all GUC changes made within
it disappear, whether they're from per-function GUC attributes or SET
commands.  This seems clearly correct.  So we need only consider cases
where no error occurs.

* A regular SET (without LOCAL) propagates clear out to the top level
and becomes the session setting, if not aborted.  Hence it must/will
override any per-function settings, either in its own function or
callers.

So it seems that only SET LOCAL within a function with per-function
GUC settings is at issue.  I think that there is a pretty strong
use-case for saying that if you have a per-function setting of a
particular variable foo, then any "SET LOCAL foo" within the function
ought to vanish at function end --- for instance a function could want
to try a few different search_path settings and automatically revert to
the caller's setting on exit.  The question is what about SET LOCAL
on a variable that *hasn't* been explicitly SET by the function
definition.  Either approach we take with it could be surprising,
but probably having it revert at function end is more surprising...

I notice BTW that we have never updated the SET reference page since
subtransactions were introduced --- it still says only that SET LOCAL
is "local to the current transaction", without a word about
subtransactions.  So we have a documentation problem anyway.  I recall
that we had some discussion during the 8.0 dev cycle about whether
having SET LOCAL's effects end at the end of the current subtransaction
was really a good idea, given that subtransactions aren't the conceptual
model the SQL spec defines, but nothing was ever done about changing
the implementation.  In fact, our current recommendation for
implementing secure SECURITY DEFINER functions (use SET LOCAL to change
search_path) really depends on that nowhere-documented behavior ...
so it's probably too late to consider changing it now.  But this would
be the time, if we ever are going to reconsider it.

Comments?
        regards, tom lane