SEO

So I coded up a patch for this, based on the idea of creating a
quasi-subtransaction that affects only GUC while entering/exiting a
function that has GUC settings attached. The specified settings are
applied as if by SET LOCAL before starting function execution, and then
they drop out during "subtransaction" exit. (I'll post the code to
pgsql-patches in a moment.)

But on reflection I realize that there are some interesting properties
to this approach:

* if you do "SET LOCAL foo" when you are in a function that has a
"SET foo" property, the setting disappears at function exit. But if
you do "SET foo" it persists. This might be OK, but it seems a bit odd.

* in fact, if you do "SET LOCAL foo" when you are in a function that has
any "SET" property at all, the setting disappears at function exit,
whether foo was one of the variables SET by the function definition or
not.

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?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> So I coded up a patch for this, based on the idea of creating a
> quasi-subtransaction that affects only GUC while entering/exiting a
> function that has GUC settings attached. The specified settings are
> applied as if by SET LOCAL before starting function execution, and then
> they drop out during "subtransaction" exit. (I'll post the code to
> pgsql-patches in a moment.)
>
> But on reflection I realize that there are some interesting properties
> to this approach:
>
> * if you do "SET LOCAL foo" when you are in a function that has a
> "SET foo" property, the setting disappears at function exit. But if
> you do "SET foo" it persists. This might be OK, but it seems a bit odd.
That seems OK - the same happens inside a BEGIN/EXCEPTION/END block, no?

> * in fact, if you do "SET LOCAL foo" when you are in a function that has
> any "SET" property at all, the setting disappears at function exit,
> whether foo was one of the variables SET by the function definition or
> not.
Hm... That is a bit surprising... Maybe all functions should create a
such GUC-only substransaction-like thing. That might create problems
for inlining - but only if you can actually change GUCs from plsql
function, which maybe you cant...

> 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'd make more
sense to me if bar would use the session's search-path, but that seems hard
to do... Especially because bar *should* use foo's searchpath if foo contained
an explicit "SET LOCAL search_path"

Or maybe I'm just crazy, and the current behavior is fine....

greetings, Florian Pflug


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Sun, Sep 02, 2007 at 12:08:00PM -0400, Tom Lane wrote:
> 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.

ISTM that's the real problem; SET LOCAL wasn't fully updated/considered
when subtransactions were added.

One way to handle this would be to have 3 different behaviors for SET:
session-level, transaction-level, and sub-transaction level. If we had
that, we could probably make an across-the-board call that all functions
operate as if in their own sub-transaction, at least when it comes to
SET.

Whatever we decide on, least-surprise would dictate that it's the
same whether you apply function-specific settings or not.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFG289hdO30qud8SkgRAhkwAJ4k03B1IZdZkKUFojPKBV XI4KR0+wCeNkqH
klxzhgdXABlRdTsH2lHKiZE=
=KtXi
-----END PGP SIGNATURE-----

On Mon, 2007-09-03 at 04:09 -0500, Decibel! wrote:
> On Sun, Sep 02, 2007 at 12:08:00PM -0400, Tom Lane wrote:
> > 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.
>
> ISTM that's the real problem; SET LOCAL wasn't fully updated/considered
> when subtransactions were added.
>
> One way to handle this would be to have 3 different behaviors for SET:
> session-level, transaction-level, and sub-transaction level. If we had
> that, we could probably make an across-the-board call that all functions
> operate as if in their own sub-transaction, at least when it comes to
> SET.

What would be the use case for that? I can't see a single reason to do a
SET LOCAL SUBTRANSACTION or whatever you'd call it. What you suggest
sounds nicely symmetrical, but I don't think we need it in practice.

ISTM that SET LOCAL is mostly superceded by per-function parameters.
Most parameters need to be tied to code, not transactions. Of course, my
wish to use synchronous_commit *was* tied to a transaction, but not a
subtransaction.

per-function parameters are sorely needed anyhow, since with session
pools we can't easily use the username for SET parameters.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Simon Riggs <simon@2ndquadrant.com> writes:
> ISTM that SET LOCAL is mostly superceded by per-function parameters.

Mostly, but not entirely. The case where you still need SET LOCAL is
where the value you want to use locally has to be computed, or where you
need to change it more than once within the function. Yet in such cases
it'd still be handy to let the SET-clause mechanism deal with the detail
of restoring the caller's value at exit.

There is also a fairly nasty backward-compatibility problem. Suppose
that security definer function OldSD uses the recommended-up-to-now
method for setting a secure search path, which I quote from the 8.2
manual:

old_path := pg_catalog.current_setting('search_path');

PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true);

-- Do whatever secure work we came for.

PERFORM pg_catalog.set_config('search_path', old_path, true);

(The set_config calls are equivalent to SET LOCAL.) Also suppose that
security definer function NewSD uses the fancy new function-local-
SET-clause method to avoid all that tedious stuff there. Now suppose
that NewSD calls OldSD. If SET LOCAL overrides SET-clauses, this
happens:

* NewSD saves outer search path and sets its own.
* OldSD saves NewSD's search path, then sets its own with SET LOCAL.
* OldSD restores NewSD's search path using SET LOCAL.
* NewSD tries to restore outer search path, but silently fails
because SET LOCAL takes precedence.
* We exit to the caller with NewSD's search path still in effect.

This scenario will surely happen in the field, and therefore I argue
that we *must* not allow SET LOCAL's effects to persist beyond the
exit from a surrounding function-local SET clause on the same variable.

I'm not sure what conclusions that leads to for other cases, though.
We don't necessarily have to be consistent between the case where
SET affects a variable and the case where it doesn't.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>> Tom Lane wrote:
> 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.
Agreed.

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

At least for me, the least surprising behaviour would be to
revert it too. Than the rule becomes "a function is always
executed in a pseudo-subtransaction that affects only GUCs"

Since at least for pl/pgsql, a function body *alreay* is a
BEGIN/END block - and therefore syntactically even looks
like a subtransaction - this seems quite logical.

And it would mean that the semantics of "SET LOCAL" won't change,
just because you add an EXCEPTION clause to the function's toplevel
BEGIN/END block.

greetings, Florian Pflug


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Florian G. Pflug" <fgp@phlo.org> writes:
> At least for me, the least surprising behaviour would be to
> revert it too. Than the rule becomes "a function is always
> executed in a pseudo-subtransaction that affects only GUCs"

Only if it has at least one SET clause. The overhead is too high
to insist on this for every function call.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>> At least for me, the least surprising behaviour would be to
>> revert it too. Than the rule becomes "a function is always
>> executed in a pseudo-subtransaction that affects only GUCs"
>
> Only if it has at least one SET clause. The overhead is too high
> to insist on this for every function call.

In that case, I agree that only variables specified in a SET-clause
should be reverted. Otherwise, adding or removing
SET-clauses (e.g, because you chose a different implementation
of a function that suddenly doesn't need regexps anymore) will
cause quite arbitrary behavior changes.

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

greetings, Florian Pflug



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Florian G. Pflug" <fgp@phlo.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?

As far as implementation, I think this can be made to happen by
rejiggering the value stacking and unstacking rules within guc.c.
I'm tempted to try to get rid of the "tentative" value slots at the
same time. That's a hangover from the pre-subtransaction
implementation, when we only had to remember one inactive value for the
case of SET followed by SET LOCAL within a transaction. Now that we
have a stack of saved values, it seems to make more sense to try to
handle this case by stacking the SET value when we hit SET LOCAL at the
same nesting level.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly