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=W1pMkXRsY9y6Mf1Xq8Xkxx/47WqNWufFX4cEQe+i8vOsp+2L4b5PJ85Uh4OTbtY5NM
	92ZBCdJXnKVsIjiC61WiTl9cCS6RFPw/gDoTIT/YSYtBRvwTHCcH1RIHY9qyaJIQPOY0
	BblhW1BmWlS2crkqa2Z2dsdR7lASuyqE4OGe4=
MIME-Version: 1.0
In-Reply-To: <24194.1272078682@sss.pgh.pa.us>
References: <u2o603c8f071004231952i36642ae6u9d6a7eae6eb6ff32@mail.gmail.com>
	<24194.1272078682@sss.pgh.pa.us>
Date: Fri, 23 Apr 2010 23:18:29 -0400
Message-ID: <z2r603c8f071004232018k9891b0f8p5fcb1708455f462d@mail.gmail.com>
Subject: Re: global temporary tables
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Fri, Apr 23, 2010 at 11:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> A couple of recent threads made got me thinking again about the idea
>> of global temporary tables. =A0There seem to be two principal issues:
>
>> 1. What is a global temporary table?
>
>> 2. How could we implement that?
>
>> Despite rereading the "idea: global temp tables" thread from April
>> 2009 in some detail, I was not able to get a clear understanding of
>> (1).
>
> I believe that the spec's distinction between global and local temp
> tables has to do with whether they are visible across module
> boundaries. =A0Since we haven't implemented modules, that distinction
> is meaningless to us. =A0In the spec, *both* types of temp tables have
> the property that the definition (schema) of the table is global
> across all sessions, and only the content of the table is session-local.
>
> This arrangement clearly is useful for some applications, but so is our
> current definition wherein different sessions can have different schemas
> for the same temp table name. =A0So eventually it'd be good to support
> both. =A0But the GLOBAL/LOCAL TEMP TABLE distinction is something entirel=
y
> different. =A0PG's behavior does not correspond to either of those.

I don't really care what we call it, although I find the GLOBAL name
convenient and descriptive.

> Your idea of using the relmapper layer to instantiate copies of temp
> tables is an interesting one. =A0It's only a small piece of the puzzle
> though. =A0In particular, what you described would result in the table
> having the same OID in all sessions, even though the relfilenodes are
> different --- amd since locking is done on the basis of OID, that's
> probably *not* what we want. =A0It would be much better for performance
> if the different sessions' versions of the table were independently
> lockable.

Well, it depends on what operation we're talking about.  For
operations that involve only the table contents, yeah, we'd like to
lock the versions independently.  But for this sort of thing:

> I also kind of wonder what is supposed to happen if someone DROPs or
> ALTERs the temp table definition ...

...not so much.  Here you REALLY want a DROP attempt to acquire an
AccessExclusiveLock that will conflict with any outstanding
AccessShareLocks.  Similarly, you're only going to be able to modify
the schema for the relation if it's not otherwise in use.

...Robert