pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporary table weirdness
5+ messages / 3 participants
[nested] [flat]
* Temporary table weirdness
@ 2002-04-29 06:20 Joseph Barillari <jbarilla@princeton.edu>
0 siblings, 2 replies; 5+ messages in thread
From: Joseph Barillari @ 2002-04-29 06:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary table weirdness
@ 2002-04-29 14:04 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joseph Barillari <jbarilla@princeton.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2002-04-29 14:04 UTC (permalink / raw)
To: Joseph Barillari <jbarilla@princeton.edu>; +Cc: pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary table weirdness
@ 2002-04-29 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joseph Barillari <jbarilla@princeton.edu>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2002-04-29 14:12 UTC (permalink / raw)
To: Joseph Barillari <jbarilla@princeton.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary table weirdness
@ 2002-04-29 16:07 Joseph Barillari <jbarilla@princeton.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Joseph Barillari @ 2002-04-29 16:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>>>>> "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 the temp 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary table weirdness
@ 2002-06-10 19:50 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2002-06-10 19:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joseph Barillari <jbarilla@princeton.edu>; pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-10 19:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-29 06:20 Temporary table weirdness Joseph Barillari <jbarilla@princeton.edu>
2002-04-29 14:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-29 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-29 16:07 ` Joseph Barillari <jbarilla@princeton.edu>
2002-06-10 19:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox