Обсуждение: BUG #5902: pl/pgsql plans are not invalidated on discard all
The following bug has been logged online: Bug reference: 5902 Logged by: Ingmar Brouns Email address: swingi@gmail.com PostgreSQL version: 9.0.3 Operating system: Fedora 13 Description: pl/pgsql plans are not invalidated on discard all Details: Hi, I ran into the pl/pgsql Plan Invalidation and search_path bug that is on the todo list http://wiki.postgresql.org/wiki/Todo#PL.2FpgSQL I was looking for a workaround to this problem, and figured that calling 'discard all', or 'discard plans' should do the trick. However, also after discard all, the plpgsql function seems to be executed with the old plan. Kind regards, Ingmar example code create schema a; create schema b; create table a.test_table(a integer); create table b.test_table(b integer); insert into a.test_table values(1); insert into b.test_table values(2); create or replace function public.foo() returns integer as $$ declare retval integer; begin select * into retval from test_table; return retval; end; $$ language plpgsql; set search_path to a,public; select public.foo(); set search_path to b,public; select public.foo(); --this is the todo as known --now lets discard the session state and try it again DISCARD ALL; DISCARD PLANS; set search_path to b,public; select public.foo(); output: CREATE SCHEMA CREATE SCHEMA CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 CREATE FUNCTION SET foo ----- 1 (1 row) SET foo ----- 1 (1 row) DISCARD ALL SET foo ----- 1 --The output is still (1) instead of (2) (1 row) version ---------------------------------------------------------------------------- -------------------------------- PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4-10), 32-bit
"Ingmar Brouns" <swingi@gmail.com> writes: > Description: pl/pgsql plans are not invalidated on discard all Yes they are. > I ran into the pl/pgsql Plan Invalidation and search_path bug that is on the > todo list > http://wiki.postgresql.org/wiki/Todo#PL.2FpgSQL > I was looking for a workaround to this problem, and figured that calling > 'discard all', or 'discard plans' should do the trick. That's not a solution because the plancache module intentionally preserves the original search_path value while replanning. This may not be what you wished for, but it's operating as intended, and changing it would break other use-cases that work today. regards, tom lane
Hi Tom, thanks for your reply. > > I was looking for a workaround to this problem, and figured that calling > > 'discard all', or 'discard plans' should do the trick. > > That's not a solution because the plancache module intentionally > preserves the original search_path value while replanning. This > may not be what you wished for, but it's operating as intended, > and changing it would break other use-cases that work today. > > regards, tom lane > We are weighing our options to deal with this problem. I'm very interested in the use cases that will break when using the current search_path instead of the original search_path when replanning. Can you tell me what will break? Thanks, Ingmar
On Tue, Mar 1, 2011 at 5:11 AM, Ingmar Brouns <swingi@gmail.com> wrote: > Hi Tom, > > thanks for your reply. > >> >> > I was looking for a workaround to this problem, and figured that calli= ng >> > 'discard all', or 'discard plans' should do the trick. >> >> That's not a solution because the plancache module intentionally >> preserves the original search_path value while replanning. =A0This >> may not be what you wished for, but it's operating as intended, >> and changing it would break other use-cases that work today. >> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane > > We are weighing our options to deal with this problem. > I'm very interested in the use cases that=A0 will break when using the > current search_path instead of the original search_path when replanning. > Can you tell me what will break? This has been much discussed in the archives. What you probably want is a proposed (but not vetted) enhancement to pl/pgsql so that the plan cache is organized around search path, so that search_path 'a' has a set of plans, search_path 'b' has another set of plans, etc. That way, you aren't constantly recompiling your functions in a pooled environment (performance would suck). One workaround is to have the function be in the schemas you are floating with search_path. I would consider wrapping your 'create function' in a script or a plpgsql function so you can automate it's creation across schemas. Another workaround is to keep all function code that touches schema private data in regular sql functions. You can also use EXECUTE inside pl/pgsql safely. merlin