Thread: faq 4.20: pl/pgsql temporary tables create/drop
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.
Merlin
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
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
# merlin.moncure@rcsonline.com / 2005-02-04 12:30:00 -0500:
> would be more appropriate in the proper documentation than in a FAQ.
Every FAQ is an excuse for proper documentation. :)
--
FreeBSD 4.10-STABLE
8:56PM up 13 hrs, 8 users, load averages: 0.00, 0.00, 0.00
Roman Neuhauser wrote: > # merlin.moncure@rcsonline.com / 2005-02-04 12:30:00 -0500: > > would be more appropriate in the proper documentation than in a FAQ. > > Every FAQ is an excuse for proper documentation. :) I disagree. The FAQs are really to document items where the place to a look for a solution would not be obvious. This seems like one of them. -- 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
# pgman@candle.pha.pa.us / 2005-02-04 17:17:47 -0500:
> Roman Neuhauser wrote:
> > # merlin.moncure@rcsonline.com / 2005-02-04 12:30:00 -0500:
> > > would be more appropriate in the proper documentation than in a FAQ.
> >
> > Every FAQ is an excuse for proper documentation. :)
>
> I disagree. The FAQs are really to document items where the place to a
> look for a solution would not be obvious. This seems like one of them.
For questions with non-obviously placed answers, the FAQ should look
like:
Q: How do I ...
A: See The Manual section X.Y.Z
As soon as you put in anything else than just pointers at the documentation,
things go awry, my favorite example is the FreeBSD Handbook chapter on disks,
and the FreeBSD FAQ entry "How do I move my installation to my new disk".
Frequently Asked Questions doesn't mean Unstructured Bits Of Info.
That's just my .02.
--
FreeBSD 4.10-STABLE
11:43PM up 15:47, 8 users, load averages: 0.08, 0.06, 0.01
>Roman Neuhauser > # merlin.moncure@rcsonline.com / 2005-02-04 12:30:00 -0500: > > would be more appropriate in the proper documentation than in a FAQ. > > Every FAQ is an excuse for proper documentation. :) > Your contributions are always welcome to any form of documentation. Proper is in the eye of the beholder; if you want it better, go for it. Best Regards, Simon Riggs
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