Re: idea: global temp tables - Mailing list pgsql-hackers

Greg Stark <stark@enterprisedb.com> wrote: 
> On Wed, Apr 29, 2009 at 7:57 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.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.
True.  I got myself thinking that without this feature people would be
dropping the table after each use, which doesn't have to be the case.
> 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.
Only if they are making the first reference to the table in that
session, and it's only unexpected if they don't know that such a
reference to a global temp table can cause the table to materialize. 
Surely you will grant that someone referencing such a table should
know what it is?
> 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.
Like I said, I have run into performance problems with temp table
creation, especially when write barriers were configured on due to
battery failure or OS misconfiguration, and the cost turned out to be
almost entirely in the creation of the disk files which support the
temp table -- base, toast, indexes, etc.  Unless you can fix the big
problems, worrying about the stuff we do optimize well will be a drop
in the bucket.
> I don't see it as friendly to make that the implicit
> behaviour for innocent looking dml operations.
Perhaps a note in the documentation of global temporary tables could
set appropriate expectations?  It seems that your whole objection to
adding the requested feature hinges on anticipation of particular user
expectations.
-Kevin