Обсуждение: 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

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