Re: global temporary tables

On Sat, Apr 24, 2010 at 1:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [ forgot to respond to this part ]
>
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> ...  I don't see the problem with DROP.
>> Under the proposed design, it's approximately equivalent to dropping a
>> table that someone else has truncated.  You just wait for the
>> necessary lock and then do it.
>
> And do *what*?  You can remove the catalog entries, but how are you
> going to make the physical storage of other backends' versions go away?
> (To say nothing of making them flush their local buffers for it.)
> If you do remove the catalog entries, won't you be cutting the knees
> out from under whatever end-of-session cleanup processing might exist
> in those other backends?

Well, if I knew for sure what the best way was to solve all of these
problems, I'd be posting a finished patch rather than a request for
comment on a design. It's not obvious to me that there's a terribly
thorny problem in the area you're concerned about, but your concern is
making me worry that I'm missing something. Why would the
end-of-session processing need the catalog entries? It seems like
whatever backend-local data structure we're using to record the
relfilenode mappings would be sufficent to nuke the backend storage,
and what else needs doing?

> The idea of the global table as a template that individual sessions
> clone working tables from would avoid most of these problems.  You
> rejected it on the grounds that ALTER would be too hard; but if you're
> blowing off ALTER anyway, that argument seems pretty unimpressive.

I don't think that avoiding the catalog churn is something to be
dismissed lightly, but I also think that cloning the table is likely
to be significantly more difficult from an implementation point of
view. Under the implementation I'm proposing, we don't need much that
is fundamentally all that new. Global temporary tables can be treated
like our existing temp tables for purposes of XLog and bufmgr, but
they don't get forced into a temporary namespace. The relation
mapping infrastructure provides a pretty good start for using a
relfilenode that isn't stored in pg_class. I've already gone through
the exercise of finding all the places where we check rd_istemp and
changing them to use macros instead (RelationNeedsWAL, IsBackendLocal,
etc.) and it's not bad.

There's a related project which I think can also leverage much of this
same infrastructure: unlogged tables. We've talked about this before,
but in short the idea is that an unlogged table behaves like a regular
table in all respects except that we never write WAL for it; and we
truncate it at shutdown and at startup. Therefore, it doesn't show up
on standby servers, and its contents are not persistent across
restarts, but performance is improved. It's suitable for things like
"the table of currently logged in users", where you don't mind forcing
everyone to log in again if the database crashes. (It might even be
possible to allow writes to unlogged tables on standby servers, though
I'm not feeling that ambitious ATM.) So:

- local temp tables exist in a temp namespace, use local buffers, and skip WAL
- global temp tables exist in a non-temp namespace, use local buffers,
and skip WAL
- unlogged tables exist in a non-temp namespace, use shared buffers,
and skip WAL
- normal tables exist in a non-temp namespace, use shared buffers, and write WAL

Thoughts?

...Robert