From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joseph Barillari <jbarilla@princeton.edu>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Temporary table weirdness
Date: Mon, 29 Apr 2002 10:12:37 -0400
Message-ID: <23409.1020089557@sss.pgh.pa.us> (raw)
In-Reply-To: <m3elgzni0f.fsf@washer.barillari.org>
References: <m3elgzni0f.fsf@washer.barillari.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jbarilla@princeton.edu
Subject: Re: Temporary table weirdness
In-Reply-To: <23409.1020089557@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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