Re: what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL

On Tue, Jul 03, 2007 at 11:49:05AM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > Tom Lane escribi�:
> > >> I rather doubt that.  The most likely implementation would involve
> > >> cloning a "template" entry into pg_class.
> >
> > > How about a new relkind which causes the table to be located in
> > > PGDATA/base/<dboid>/pg_temp_<backendid>/<relfilenode>
> > > So each backend can have its own copy of the table with the same
> > > relfilenode; there's no need for extra catalog entries.
> >
> > Uh-huh.  And what do you do with relpages, reltuples, relfrozenxid, and
> > pg_statistic entries?  What if one backend wants to TRUNCATE or CLUSTER
> > its copy (requiring a new relfilenode)?  Where does ALTER TABLE fit into
> > this?
>
> And what is the use-case for this functionality?  What does it give us
> that we don't already have?

The use case is any system that uses temp tables in an OLTP setting,
which certainly isn't uncommon. The problem is that today (and as well
with a global temp table that is still writing to the catalogs) is that
every OLTP operation that creates or drops a temp table is doing DDL.
At best, that leads to a lot of catalog bloat. Right now, it appears to
also expose some race conditions (we've got a customer that's been bit
by this and we've been able to reproduce some odd behavior in the lab).
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)