Re: idea: global temp tables

On Wed, Apr 29, 2009 at 7:57 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> But the scaling issues are there already with temp tables.  This
> actually makes it better, not worse, because the table can be
> materialized once per session, not once per request.

Currently you have to issue CREATE TABLE and associated DDL manually.
That makes it clear to the user that they're executing DDL and should
expect it to behave like DDL.

I don't understand what you mean by a cost once per request. You only
have to create the temporary table on the first request. If you can't
tell which is the first request you only have to test whether it
exists which doesn't incur the consequences that ddl incurs.

What we're talking about means that when someone issues "SELECT * FROM
cache" they're going to -- unexpected -- be a) turning their virtual
transaction id into a real transaction id b) creating a new entry in
pg_catalog and its indexes c) wal logging the new pg_catalog entry
(including having to fsync at commit time) d) acquiring an exclusive
lock on the new entry.

There have been posts by people who were bitten by expecting that they
could create temporary work tables for short frequently run queries
who didn't realize that would mean pg_class would have to be vacuumed
every few minutes and that it would slow down every index lookup for
table names. I don't see it as friendly to make that the implicit
behaviour for innocent looking dml operations.

--
greg