Thread: plpgsql temporary table problem
Hi people,
I seem to have a problem with repeated temporary table creation in
a plpgsql function. The problem is illustrated below.
I'm running 7.2.1 on Linux. I should add that I just upgraded from 7.1.3
and
had no problems - congratulations to you all. (I have no idea on
whether this
problem occurred on 7.1.3, its part of new code I've just written.)
The second "select parent_copy(...) " statement below gives the message:
NOTICE: Error occurred while executing PL/pgSQL function parent_copy
NOTICE: line 17 at SQL statement
ERROR: Relation 2398261 does not exist
Cheers,
Geoff Russell
/*
* sample illustrating either a "create temp table ... " problem
* or perhaps just something I don't understand!
*/
/* first make some tables and data */
drop sequence parent_id_seq;
drop table parent;
drop table child;
create table parent (
id serial,
data integer
);
drop table child;
create table child (
parent_id integer,
data integer
);
insert into parent (data) values(1);
insert into child (parent_id,data) values(1,1);
insert into child (parent_id,data) values(1,2);
insert into child (parent_id,data) values(1,3);
/* now a pgsql function to copy children to a new parent */
drop FUNCTION parent_copy (integer);
CREATE FUNCTION parent_copy (integer) RETURNS integer as '
DECLARE
fromid alias for $1;
newparentid integer;
BEGIN
select into newparentid nextval(''parent_id_seq'');
/* make the new parent and add it to table
* (NOTE:
* it would be really nice to use a RECORD variable as follows.
* parentrecord RECORD;
* select into parentrecord select * from parent where
id=fromid;
* parentrecord.id=newparentid;
* insert into parent parentrecord;
* )
*/
insert into parent (id,data) values (newparentid,5);
create temp table tmpchild as select * from child where
parent_id=fromid ;
update tmpchild set parent_id=newparentid;
insert into child select * from tmpchild;
drop table tmpchild;
return newparentid;
END; '
LANGUAGE 'plpgsql';
select parent_copy(1);
select parent_copy(2);
select * from parent;
select * from child;
--
Geoff,
geoff@austrics.com.au | Phone: +618-8332-5069
6 Fifth Ave, St Morris, SA 5068 | Fax: +618-8364-1543
This is a known problem. You need to use EXECUTE CREATE TABLE in
PL/pgSQL so that the saved plpgsql function is not used and it is
reparsed for each call. I believe every reference to the TEMP table has
to be an EXECUTE. I think this will be fixed in 7.3 because the temp
tables will exist in a separate schema.
---------------------------------------------------------------------------
Geoff Russell wrote:
> Hi people,
>
> I seem to have a problem with repeated temporary table creation in
> a plpgsql function. The problem is illustrated below.
>
> I'm running 7.2.1 on Linux. I should add that I just upgraded from 7.1.3
> and
> had no problems - congratulations to you all. (I have no idea on
> whether this
> problem occurred on 7.1.3, its part of new code I've just written.)
>
> The second "select parent_copy(...) " statement below gives the message:
>
> NOTICE: Error occurred while executing PL/pgSQL function parent_copy
> NOTICE: line 17 at SQL statement
> ERROR: Relation 2398261 does not exist
>
> Cheers,
> Geoff Russell
>
> /*
> * sample illustrating either a "create temp table ... " problem
> * or perhaps just something I don't understand!
> */
>
> /* first make some tables and data */
> drop sequence parent_id_seq;
> drop table parent;
> drop table child;
> create table parent (
> id serial,
> data integer
> );
> drop table child;
> create table child (
> parent_id integer,
> data integer
> );
> insert into parent (data) values(1);
> insert into child (parent_id,data) values(1,1);
> insert into child (parent_id,data) values(1,2);
> insert into child (parent_id,data) values(1,3);
> /* now a pgsql function to copy children to a new parent */
> drop FUNCTION parent_copy (integer);
> CREATE FUNCTION parent_copy (integer) RETURNS integer as '
> DECLARE
> fromid alias for $1;
> newparentid integer;
> BEGIN
> select into newparentid nextval(''parent_id_seq'');
> /* make the new parent and add it to table
> * (NOTE:
> * it would be really nice to use a RECORD variable as follows.
> * parentrecord RECORD;
> * select into parentrecord select * from parent where
> id=fromid;
> * parentrecord.id=newparentid;
> * insert into parent parentrecord;
> * )
> */
> insert into parent (id,data) values (newparentid,5);
> create temp table tmpchild as select * from child where
> parent_id=fromid ;
> update tmpchild set parent_id=newparentid;
> insert into child select * from tmpchild;
> drop table tmpchild;
> return newparentid;
> END; '
> LANGUAGE 'plpgsql';
>
> select parent_copy(1);
> select parent_copy(2);
> select * from parent;
> select * from child;
>
>
> --
> Geoff,
>
> geoff@austrics.com.au | Phone: +618-8332-5069
> 6 Fifth Ave, St Morris, SA 5068 | Fax: +618-8364-1543
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ... I believe every reference to the TEMP table has
> to be an EXECUTE. I think this will be fixed in 7.3 because the temp
> tables will exist in a separate schema.
No, the separate schema won't help matters a bit.
Plan dependency tracking (to allow us to drop invalidated cached plans)
would help.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > ... I believe every reference to the TEMP table has
> > to be an EXECUTE. I think this will be fixed in 7.3 because the temp
> > tables will exist in a separate schema.
>
> No, the separate schema won't help matters a bit.
>
> Plan dependency tracking (to allow us to drop invalidated cached plans)
> would help.
Oh, the old temp code mapped a temp name to a system table name that was
stored in the plpgsql function cache. I thought the new code would not
make that mapping until function runtime. Does the function cache keep
a physical table name in the cache with schemas, or maybe does it store
the oid of the table? It probably is oid, and that's why the schema
changes will not fix it.
I have update our TODO item to explain the solution:
o Fix problems with complex temporary table creation/destruction
without using PL/PgSQL EXECUTE, needs cached plan invalidation
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> No, the separate schema won't help matters a bit.
> Oh, the old temp code mapped a temp name to a system table name that was
> stored in the plpgsql function cache. I thought the new code would not
> make that mapping until function runtime.
The point is that cached plans refer to tables by OID.
regards, tom lane
On Sun, 14 Apr 2002, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > ... I believe every reference to the TEMP table has > > > to be an EXECUTE. I think this will be fixed in 7.3 because the temp > > > tables will exist in a separate schema. Yes, if I make EVERY reference to the TEMP table an execute, then it works. I don't understand the rest of your discussion, but thanks for the work around. I'm having trouble with posting to the news group at the moment (and I can't seem to get back on the digest subscription list, despite numerous attempts to subscribe after being mysteriously dropped), so email will have to do. Many thanks, Geoff Russell - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6 Fifth Ave +618-8332-5069 (Home) | St Morris SA 5068 +618-8207-2029 (Work) | geoff@austrics.com.au Adelaide, AUSTRALIA +618-8364-1543 (Fax-Home) |