pl/pgsql Plan Invalidation and search_path

Greetings,

In doing some test on 8.3RC2, I was dismayed to discover that the
pl/pgsql plan invalidation logic added doesn't consider changing the
search_path to invalidate a plan.

Our case is where we have a number of schemas with identical table
structures but differing table contents. We then have functions which
operate across the tables in those schemas. For our functions which
build up a string and then execute, everything is fine (though the
command has to be re-planned every time). For those functions where
we don't actually need to build up the query dynamically, things work
provided we only work in one schema during a session.

If we change the search_path after having run the function, the
function doesn't pick up on the new tables (it uses the same ones it
used in the first run). This can be pretty frustrating and I had
really hoped that the plan invalidation added in 8.3 would handle this
case. Here's an example:

set search_path=sfrost;
create table a (col1 integer);
insert into a values (1);

create or replace function test1 () returns integer as $_$
declare
myint integer;
begin
select into myint col1 from a;
return myint;
end; $_$ language plpgsql;

set search_path=sfrost2;
create table a (col1 integer);
insert into a values (2);

set search_path=sfrost;
select test1(); -- returns '1'

set search_path=sfrost2;
select sfrost.test1(); -- *also* returns '1', instead of '2'

Would it be possible to have this case handled?

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> In doing some test on 8.3RC2, I was dismayed to discover that the
> pl/pgsql plan invalidation logic added doesn't consider changing the
> search_path to invalidate a plan.

We never considered it so before, either. The plancache code goes out
of its way to maintain the same path that was used initially, and
I think that's what it should do: a cache module should avoid letting
the semantics of what it's cached change without the caller's knowledge.
If we were to change this, we'd probably have to think in terms of
making the active search_path be part of the lookup key for cached plans.

> Would it be possible to have this case handled?

It's far too late to reconsider this point for 8.3. If you want to
bring it up for 8.4, we could think about what the behavioral and
performance implications would really be. In the meantime, the answer
is the same as it's always been: if that's what you want, use EXECUTE.

regards, tom lane

On Jan 27, 2008 10:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > In doing some test on 8.3RC2, I was dismayed to discover that the
> > pl/pgsql plan invalidation logic added doesn't consider changing the
> > search_path to invalidate a plan.
>
> We never considered it so before, either. The plancache code goes out
> of its way to maintain the same path that was used initially, and
> I think that's what it should do: a cache module should avoid letting
> the semantics of what it's cached change without the caller's knowledge.
> If we were to change this, we'd probably have to think in terms of
> making the active search_path be part of the lookup key for cached plans.
>
> > Would it be possible to have this case handled?
>
> It's far too late to reconsider this point for 8.3. If you want to
> bring it up for 8.4, we could think about what the behavioral and
> performance implications would really be. In the meantime, the answer
> is the same as it's always been: if that's what you want, use EXECUTE.

For the record, IMO it would on balance be better to have the plan
invalidate when setting the search path. This is a special case but a
reasonable one, and the surprising behavior should be weighed against
a bigger, more unpleasant surprise when the plans don't change. Also,
I'd argue that with with this change hides the inner workings of the
plan caching, eliminating one of the last corner cases where you have
to deal with it outside of performance considerations.

merlin

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On Jan 27, 2008 10:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If we were to change this, we'd probably have to think in terms of
>> making the active search_path be part of the lookup key for cached plans.

> For the record, IMO it would on balance be better to have the plan
> invalidate when setting the search path.

I think that the actual use-case for this would likely involve
repetitive execution of a function F against various search_path
settings, and so what we'd want is to cache the appropriate plan for
each path setting, not just blow away the whole cache when search_path
changes. But the whole thing is something to investigate for 8.4.

regards, tom lane

Add to pl/pgsql TODO:

o Consider invalidating the cache or keeping seperate cached
copies when search_path changes

http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php

---------------------------------------------------------------------------

Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > In doing some test on 8.3RC2, I was dismayed to discover that the
> > pl/pgsql plan invalidation logic added doesn't consider changing the
> > search_path to invalidate a plan.
>
> We never considered it so before, either. The plancache code goes out
> of its way to maintain the same path that was used initially, and
> I think that's what it should do: a cache module should avoid letting
> the semantics of what it's cached change without the caller's knowledge.
> If we were to change this, we'd probably have to think in terms of
> making the active search_path be part of the lookup key for cached plans.
>
> > Would it be possible to have this case handled?
>
> It's far too late to reconsider this point for 8.3. If you want to
> bring it up for 8.4, we could think about what the behavioral and
> performance implications would really be. In the meantime, the answer
> is the same as it's always been: if that's what you want, use EXECUTE.
>
> 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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> Add to pl/pgsql TODO:
>
> o Consider invalidating the cache or keeping seperate cached
> copies when search_path changes
>
> http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php

IMO, Tom's idea, namely to keep separate cache plans for various
search_path settings, is a much stronger proposal and should probably
get the 'todo'.

merlin

Merlin Moncure wrote:
> On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > Add to pl/pgsql TODO:
> >
> > o Consider invalidating the cache or keeping seperate cached
> > copies when search_path changes
> >
> > http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php
>
> IMO, Tom's idea, namely to keep separate cache plans for various
> search_path settings, is a much stronger proposal and should probably
> get the 'todo'.

Done:

o Consider keeping seperate cached copies when search_path changes

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +