Re: idea: global temp tables - Mailing list pgsql-hackers
From | Greg Stark |
---|---|
Subject | Re: idea: global temp tables |
Date | |
Msg-id | 4136ffa0904291028q8dd226bu6b47ed2dc6f4b3b5@mail.gmail.com Whole thread Raw |
In response to | Re: idea: global temp tables ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
List | pgsql-hackers |
On Wed, Apr 29, 2009 at 4:24 PM, Kevin Grittner <Kevin.Grittner@wicourts.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
pgsql-hackers by date: