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