From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200502041703.j14H3kE12325@candle.pha.pa.us>
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A760F@Herge.rcsinc.local>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Date: Fri, 4 Feb 2005 12:03:46 -0500 (EST)
Cc: pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

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