DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=tMebr6UgeBF2MO/Ty1BgS18jRSxan4qoW1bMwdTNUQ/o523AJUjTBVaeuEI4O8G+KJ
	E63tJ4E6XI1kar2wmr7hxXYx8xTy1wYIZEk3ohBR2h6E/8FqB+CdOK93XsUq913SqSM8
	u5maew23SN/KPzNEbaxjspUcbH5WZGaVJjGCk=
MIME-Version: 1.0
In-Reply-To: <FD5C55A5-5003-4C4E-8DC6-E124A13E0B68@decibel.org>
References: <fca910820578d251cc1d5647793c9a67@biglumber.com>
	<7796.1272125493@sss.pgh.pa.us>
	<k2y603c8f071004241016w48b9d39an2dbf6d9df2bad495@mail.gmail.com>
	<9319.1272130283@sss.pgh.pa.us>
	<FD5C55A5-5003-4C4E-8DC6-E124A13E0B68@decibel.org>
Date: Sat, 24 Apr 2010 21:14:44 -0400
Message-ID: <y2u603c8f071004241814x10e29bc8pb46cad5edc0e2a5b@mail.gmail.com>
Subject: Re: global temporary tables
From: Robert Haas <robertmhaas@gmail.com>
To: Jim Nasby <decibel@decibel.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Greg Sabino Mullane <greg@turnstep.com>,
	pgsql-hackers@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Sat, Apr 24, 2010 at 8:47 PM, Jim Nasby <decibel@decibel.org> wrote:
> On Apr 24, 2010, at 12:31 PM, Tom Lane wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> At least AIUI, the use case for this feature is that you want to avoid
>>> creating "the same" temporary table over and over again.
>>
>> The context that I've seen it come up in is that people don't want to
>> clutter their functions with create-it-if-it-doesn't-exist logic,
>> which you have to have given the current behavior of temp tables.
>> Any performance gain from reduced catalog churn would be gravy.
>>
>> Aside from the DROP problem, I think this implementation proposal
>> has one other big shortcoming: what are you going to do about
>> table statistics? =A0In many cases, you really *have* to do an ANALYZE
>> once you've populated a temp table, if you want to get decent plans
>> for it. =A0Where will you put those stats?
>
> One possibility: rename the existing pg_stats to pg_stats_permanent. Crea=
te a global temporary table called pg_stats_temporary. pg_stats becomes a u=
nion of the two. I know the backend wouldn't be able to use the view, but h=
opefully access to statistics goes through a limited set of functions so th=
at teaching them about the two different tables isn't hard.

Yeah, I don't think that would be too horrible.  Part of me feels like
you'd want to have the ability to store stats for a global temp table
in either one of those tables depending on use-case, but I'm also
reluctant to invent a lot of new syntax for a very limited use case.

> As for cleanup and storage questions; what about having temp objects live=
 in pgsql_tmp? I'm thinking create a directory under pgsql_tmp for a backen=
d PID the first time it creates a temp object (global or local) and create =
the files in there. That also means that we don't have to come up with diff=
erent relfilenodes for each backend.

That would impose a couple of implementation restrictions that don't
seem necessary.  One, it would imply ignoring reltablespace.  Two, it
would prohibit (or at least complicate) allowing a backend to CLUSTER
or REINDEX its own private copy of the rel.

> On the other hand, some layer (presumably smgr) would need to understand =
whether a relation was temporary or not. If we do that, cleanup is easy: yo=
u can remove any directories that no longer have a running PID. For forensi=
cs you probably only want to do that automatically when a backend starts an=
d discovers it already has a directory, though we should also provide an ad=
ministrator function that will clobber all directories that no longer have =
backends.

Unfortunately, I don't see much alternative to making smgr know
something about the temp-ness of the relation, though I'm hoping to
keep the smgr surgery to an absolute minimum.  Maybe what we could do
is incorporate the backend ID or PID into the file name when the
relation is temp.  Then we could scan for and nuke such files pretty
easily.  Otherwise I can't really think how to make it work.

...Robert