Re: global temporary tables - Mailing list pgsql-hackers

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.  There 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.  Since we haven't implemented modules, that distinction
> is meaningless to us.  In 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.  So eventually it'd be good to support
> both.  But the GLOBAL/LOCAL TEMP TABLE distinction is something entirely
> different.  PG'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.  It's only a small piece of the puzzle
> though.  In 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.  It 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