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

BUG #5902: pl/pgsql plans are not invalidated on discard all

From: "Ingmar Brouns" <swingi(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5902: pl/pgsql plans are not invalidated on discard all
Date: 2011-02-28 14:09:57
Message-ID: 201102281409.p1SE9vcS050393@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5902
Logged by:          Ingmar Brouns
Email address:      swingi(at)gmail(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-02-28 15:44:36
Subject: Re: BUG #5902: pl/pgsql plans are not invalidated on discard all
Previous:From: John R PierceDate: 2011-02-28 03:28:44
Subject: Re: BUG #5901: Delayed Write Failed

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