Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 3F3059FB2E7
	for <pgsql-hackers-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 17:58:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 22898-04 for
	<pgsql-hackers-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 17:57:52 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id B7E8D9FA2EE
	for <pgsql-hackers@postgresql.org>;
	Tue,  3 Jul 2007 17:57:55 -0300 (ADT)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 300C55648B; Tue,  3 Jul 2007 15:57:54 -0500 (CDT)
Received: (hashcash-sendmail, from uid 1001);
	Tue, 3 Jul 2007 15:57:46 -0500
Date: Tue, 3 Jul 2007 15:57:46 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@commandprompt.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: what is difference between LOCAL and GLOBAL TEMP TABLES in
	PostgreSQL
Message-ID: <20070703205746.GW85497@nasby.net>
References: <27473.1183431616@sss.pgh.pa.us>
	<200707031549.l63Fn5W25742@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="7d9k4kQHj3EPdFyS"
Content-Disposition: inline
In-Reply-To: <200707031549.l63Fn5W25742@momjian.us>
X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.13 (2006-08-11)
X-Hashcash: 1:20:070703:bruce@momjian.us::UguZ8LizWtByHcgK:0D1rx
X-Hashcash: 
 1:20:070703:tgl@sss.pgh.pa.us::9lJnswWRzRwr/y9X:00000000000000000000
	000000000000000000000000PXB+
X-Hashcash: 
 1:20:070703:alvherre@commandprompt.com::NK+iiRKbW3tpIdkx:00000000000
	0000000000000000000000000ZcI
X-Hashcash: 
 1:20:070703:pavel.stehule@gmail.com::EIRlhe0Dv6oGzmC8:00000000000000
	0000000000000000000000004SkF
X-Hashcash: 
 1:20:070703:pgsql-hackers@postgresql.org::KPfsd5KUJSK2dHPm:000000000
	0000000000000000000000001oaj
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/92
X-Sequence-Number: 105007


--7d9k4kQHj3EPdFyS
Content-Type: text/plain; charset=unknown-8bit
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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=F3:
> > >> I rather doubt that.  The most likely implementation would involve
> > >> cloning a "template" entry into pg_class.
> >=20
> > > 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.
> >=20
> > 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?
>=20
> 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).
--=20
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--7d9k4kQHj3EPdFyS
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGirhKdO30qud8SkgRAgZbAJ9Lfy+kpfQnw8dEKYQLGIhUbJb4vwCgqLqV
YIB1k5wkvP5rv6bVaPevwiM=
=xxRz
-----END PGP SIGNATURE-----

--7d9k4kQHj3EPdFyS--