Skip site navigation (1) Skip section navigation (2)

pl/pgsql Plan Invalidation and search_path

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: pl/pgsql Plan Invalidation and search_path
Date: 2008-01-28 03:17:23
Message-ID: 20080128031723.GA5031@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-01-28 03:45:13
Subject: Re: pl/pgsql Plan Invalidation and search_path
Previous:From: Tom LaneDate: 2008-01-28 02:51:48
Subject: Re: GSSAPI doesn't play nice with non-canonical host names

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group