Re: idea: global temp tables

On Wed, Apr 29, 2009 at 4:24 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> I understand not everyone has a
> need for such a thing, but isn't that true of most features in
> PostgreSQL?

Well I think implementing a feature which only works if it's used at
low transaction rates as a convenience wrapper for a single command
seems like a lot of work for little gain.

> If you're saying we can implement the standard's global temporary
> tables in a way that performs better than current temporary tables,
> that's cool.  That would be a nice "bonus" in addition to the
> application programmer convenience and having another tick-mark on the
> standards compliance charts.

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 claim there's an implied expectation that by predefining
these schema definitions you eliminate the overhead of DDL creating
and dropping tables on the fly. That you can basically explicitly code
up algorithms which might be too complex or detailed for an SQL query
which get executed as high transaction rate DML using temporary
storage just as our SQL engine uses it in materialize nodes and sort
nodes.

Just to give a real-world example, think of web pages that do paging
of moderately complex query results. You often want to calculate the
total number of matches and then also return a subset of those
matches. Currently the only practical way to do it is to execute the
query twice.Creating a temporary table for this purpose would
transform your simple read-only DML into a complex DDL operation you
can't expose to the masses without a lot of precautions and extra
consequences.

> Do you think that's feasible?  If not,
> the feature would be useful to some with the same performance that
> temporary tables currently provide.

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.

It could still use local buffers but it could use the global relcache,
invalidation, locks, etc. I think we would hav eto take a
session-level access lock as soon as we put any data in our local
store. And each DDL operation would have to be visited to see whether
it needs special behaviour for locally stored tables. I suspect most
of them will only be able to be handled if there are no active
sessions using the table so they'll basically be no-ops except for the
catalog changes.

--
greg