From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
Date: Fri, 4 Feb 2005 12:03:46 -0500 (EST)
Message-ID: <200502041703.j14H3kE12325@candle.pha.pa.us> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A760F@Herge.rcsinc.local>
Merlin Moncure wrote:
> The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround
> for the table OID caching problem of temp tables in pg/pgsql functions.
> While this is ok, it fails to suggest that besides the initial
> create/drop statements, every statement that touches the table must also
> be dynamic.
>
> With 8.0 comes pl/pgsql exception handlers...in the beginning of
> function execution one might do the following:
> begin
> begin
> delete from temp_table; -- temp table
> exception
> when others then
> perform create temp temp_table [...]
> end;
> As long as the table structure does not change between function
> executions, this can be a more elegant approach to dealing with this
> problem. Pre 8.0, I would have suggested to initialize all temporary
> tables in a special function, but this still requires special handling
> code when the connection gets broken, etc. I think it would be helpful
> to erstwhile pl/pgsql developers to list this alternative method here.
Uh, the FAQ reads:
<H4><A name="4.20">4.20</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?</H4>
<P>PL/PgSQL caches function scripts, and an unfortunate side effect
is that if a PL/PgSQL function accesses a temporary table, and that
table is later dropped and recreated, and the function called again,
the function will fail because the cached function contents still
point to the old temporary table. The solution is to use
<SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
will cause the query to be reparsed every time.</P>
What should be changed? I see it saying "function accesses a temporary
table". The word "access" suggests all access, not just create/drop.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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-docs@postgresql.org
Cc: pgman@candle.pha.pa.us, merlin.moncure@rcsonline.com
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
In-Reply-To: <200502041703.j14H3kE12325@candle.pha.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