Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com>
Cc: pgsql-docs(at)postgresql(dot)org
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
Date: 2005-02-15 04:36:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-docs
Yes, you are right, the question was inaccurate.  Here is the updated

    <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(at)postgresql(dot)org)

  Bruce Momjian                        |
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-docs by date

Next:From: Bruce MomjianDate: 2005-02-15 04:47:43
Subject: Re: How the planner uses statistics
Previous:From: Bruce MomjianDate: 2005-02-15 04:16:57
Subject: Re: Instructions for Linux ipc config

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group