Re: faq 4.20: pl/pgsql temporary tables create/drop

Yes, you are right, the question was inaccurate.  Here is the updated
text:

    <H4><A name="4.20">4.20</A>) Why do I get "missing oid" errors when
    accessing temporary tables in PL/PgSQL functions?</H4>

---------------------------------------------------------------------------

Merlin Moncure wrote:
> Bruce Momijan wrote:
> > 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.
> > 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.
>
> You are 100% correct.  But something still doesn't feel right.
>
> Namely, the answer answers the question, "why can't I reliably access
> temporary tables in pg/pgsql functions?"  Note that a temporary table
> not created in a pg/pgsql function will still have this behavior.  So,
> really, it is the question that is misleading, not the answer.
>
> One possible re-phrasing would be:
> "Why do temporary tables in PL/PgSQL functions give me "missing oid"
> errors?"
>
> I think this matches the existing answer much better.  Given further
> consideration, my previous suggestions regarding using exception handing
> to manage temporary table construction, etc. would be more appropriate
> in the proper documentation than in a FAQ.
>
> I will say that for most cases of usage of temporary tables for storage
> from within pg/pgsql functions, using dynamic sql is probably not the
> optimial solution unless dynamic sql is more generally preferred.
>
> Merlin
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  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