Re: Do TEMP Tables have an OID? Can this be a problem if used too frequently?

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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------