Re: idea: global temp tables

Greg Stark <stark@enterprisedb.com> writes:
> Well I claim it's not just a nice bonus but is the difference between
> implementing something which falls technically within the standard's
> rules but fails to actually be useful for the standard's intended
> purpose.

I agree with Kevin's objection that you are unfairly raising the bar
for this feature by demanding a performance improvement to go along
with a functionality change.  The use-case for this feature is to
simplify application logic by allowing apps to assume that a temp
table exists without having to create it at the start of a session.
That's particularly handy in connection-pooling scenarios, for instance.
Currently, you have to have some sort of "if exists" check, and you
pay just as much in catalog thrashing as you would if the feature
was present without any catalog optimization.

It would be great to find a way to avoid the catalog thrashing,
but I completely disagree with a point of view that says we can't
have this without solving that first.  It's an improvement on the
current state of affairs anyway.

> I've been thinking about Alvaro's idea of a separate smgr. If you had
> a single pg_class entry for all sessions but the smgr knew to store
> the actual data for it in a session-local file, either in a
> session-specific tablespace or using the same mechanism the temporary
> files use to direct data then the backend would basically never know
> it wasn't a regular table.

1. pg_statistic.

2. How you going to have transaction-safe behavior for things like
TRUNCATE, if you don't have an updatable private catalog entry to keep
the current relfilenode in?

> It could still use local buffers but it could use the global relcache,
> invalidation, locks, etc.

Locks would be another big problem: if only smgr knows that different
instances of the table are different, then different backends' locks
would conflict, which would be Bad.  This might not matter for simple
read/update, but again TRUNCATE is a counterexample of something that
is likely to be needed and should not cause cross-backend conflicts.
        regards, tom lane