Thread: Do TEMP Tables have an OID? Can this be a problem if used too frequently?
Hi I've just written a search function which creates a temp table, preforms some reasoning on it returning results then drops it again. I'm using temp tables in an attempt to gain efficiency (not repeating work between one section of the function and another). However I'm worried that there may be some pit falls in doing this. I'm especially worried about OIDs. Does creating a temp table assign an OID to the table? If so am I right to assume that, if the function is used too frequently, it could cause the database to crash by wraping OIDs? Thanks very much for your time Phil
Re: Do TEMP Tables have an OID? Can this be a problem if used too frequently?
From
Raymond O'Donnell
Date:
On 30/04/2009 10:01, Phil Couling wrote: > I've just written a search function which creates a temp table, preforms > some reasoning on it returning results then drops it again. > I'm using temp tables in an attempt to gain efficiency (not repeating > work between one section of the function and another). > > However I'm worried that there may be some pit falls in doing this. I'm > especially worried about OIDs. Yes, a temp table does get an OID. You haven't said what version of PostgreSQL you're on, but one pitfall in earlier versions (pre-8.3 I think) is because execution plans for functions are cached, the first call to the function will work fine, but subsequent calls will attempt to reference the temp table using the old OID - boom! The work-around to this is to construct dynamically any queries that touch the temp table, and then use EXECUTE to run them. There's a FAQ entry about it here: http://wiki.postgresql.org/wiki/FAQ#Why_do_I_get_.22relation_with_OID_.23.23.23.23.23_does_not_exist.22_errors_when_accessing_temporary_tables_in_PL.2FPgSQL_functions.3F > If so am I right to assume that, if the function is used too frequently, > it could cause the database to crash by wraping OIDs? I'd imagine that this depends on how often the database is VACUUMed. HTH, Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
"Raymond O'Donnell" <rod@iol.ie> writes: > On 30/04/2009 10:01, Phil Couling wrote: >> If so am I right to assume that, if the function is used too frequently, >> it could cause the database to crash by wraping OIDs? > I'd imagine that this depends on how often the database is VACUUMed. Wrapping around the OID counter does not create any problem in any reasonably modern version of Postgres. regards, tom lane