Thread: Temporary table weirdness
Hi, I have a function that creates and destroys temporary tables in the course of its work, and have run into trouble if the function is executed more than once during a database session. A small proof-of-concept is attached below: cal=> create or replace function frob() returns integer as 'begin create temporary table foo(bar INT); insert into foo (bar)values (1); drop table foo; return 1; end;' language 'plpgsql'; CREATE (seems pretty standard) cal=> select frob();frob ------ 1 (1 row) (OK so far...) cal=> select frob(); NOTICE: Error occurred while executing PL/pgSQL function frob NOTICE: line 1 at SQL statement ERROR: Relation 68141 does not exist On the second call, PostgreSQL chokes. Any suggestions as to why would be appreciated. Oddly enough, redefining the function eliminates the error for the next function call. Thanks, Joe
Yes, this is a known problem. You need to use plpgsql EXECUTE command for any function query that deals with temp tables. --------------------------------------------------------------------------- Joseph Barillari wrote: Checking application/pgp-signature: FAILURE -- Start of PGP signed section. > Hi, > > I have a function that creates and destroys temporary tables in the > course of its work, and have run into trouble if the function > is executed more than once during a database session. A small > proof-of-concept is attached below: > > cal=> create or replace function frob() returns integer as 'begin create temporary table foo(bar INT); insert into foo(bar) values (1); drop table foo; return 1; end;' language 'plpgsql'; > CREATE > > (seems pretty standard) > > cal=> select frob(); > frob > ------ > 1 > (1 row) > > (OK so far...) > > cal=> select frob(); > NOTICE: Error occurred while executing PL/pgSQL function frob > NOTICE: line 1 at SQL statement > ERROR: Relation 68141 does not exist > > On the second call, PostgreSQL chokes. Any suggestions as to why > would be appreciated. > > Oddly enough, redefining the function eliminates the error for the > next function call. > > Thanks, > > Joe -- End of PGP section, PGP failed! -- 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, Pennsylvania19026
Joseph Barillari <jbarilla@princeton.edu> writes:
> I have a function that creates and destroys temporary tables in the
> course of its work, and have run into trouble if the function
> is executed more than once during a database session. A small
> proof-of-concept is attached below:
> cal=3D> create or replace function frob() returns integer as 'begin create =
> temporary table foo(bar INT); insert into foo (bar) values (1); drop table =
> foo; return 1; end;' language 'plpgsql';
This should be in the FAQ :-(. Since plpgsql caches query plans, it
will fall over the second time through this code, because the temp table
is no longer the same table (same OID) as it was the first time ---
but the cached plan for the INSERT still has the old OID.
There's a TODO item for plpgsql to detect changes of schema that affect
its cached plans, and drop the cache; but it's not exactly trivial to
do.
In the meantime, you need to use EXECUTE to defeat the plan caching for
every plpgsql query that touches the temp table. Another answer is to
arrange to create the temp table only once per session, but that's
harder.
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> In the meantime, you need to use EXECUTE to defeat the plan TL> caching for every plpgsql query that touches thetemp table. TL> Another answer is to arrange to create the temp table only TL> once per session, but that's harder. That's actually what I did -- the client programs in this application have to call a function to create the various temporary tables at the start of their database sessions. It's a bit of a kludge, but there are only a few client applications which talk to this database. Regards, Joe Barillari
FAQ updated with this, item 4.26. --------------------------------------------------------------------------- Tom Lane wrote: > Joseph Barillari <jbarilla@princeton.edu> writes: > > I have a function that creates and destroys temporary tables in the > > course of its work, and have run into trouble if the function > > is executed more than once during a database session. A small > > proof-of-concept is attached below: > > > cal=3D> create or replace function frob() returns integer as 'begin create = > > temporary table foo(bar INT); insert into foo (bar) values (1); drop table = > > foo; return 1; end;' language 'plpgsql'; > > This should be in the FAQ :-(. Since plpgsql caches query plans, it > will fall over the second time through this code, because the temp table > is no longer the same table (same OID) as it was the first time --- > but the cached plan for the INSERT still has the old OID. > > There's a TODO item for plpgsql to detect changes of schema that affect > its cached plans, and drop the cache; but it's not exactly trivial to > do. > > In the meantime, you need to use EXECUTE to defeat the plan caching for > every plpgsql query that touches the temp table. Another answer is to > arrange to create the temp table only once per session, but that's > harder. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- 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, Pennsylvania19026