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@xxxxxx Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
