On Fri, Aug 21, 2009 at 1:38 AM, Alvaro Herrera<alvherre@xxxxxxxxxxxxxxxxx> wrote: > Greg Stark wrote: > >> It would be nice to have a solution to that where you could create >> lightweight temporary objects which belong to an "application session" >> which can be picked up by a different database connection each go >> around. > > It would be useful: > > CREATE SCHEMA session1234 UNLOGGED > CREATE TABLE hitlist ( ... ); > > Each table in the "session1234" schema would not be WAL-logged, and > would be automatically dropped on crash recovery (actually the whole > schema would be). But while the server is live it behaves like a > regular schema/table and can be seen by all backends (i.e. not temp) I don't think unlogged is the only, and perhaps not even the most important, desirable property. I would want these objects not to cause catalog churn. I might have thousands of sessions being created all the time and creating new rows and index pointers which have to be vacuumed would be a headache. I would actually want the objects to be invisible to other sessions, at least by default. You would have to have the handle for the application session to put them into your scope and then you would get them all en masse. This isn't so much for security -- I would be fine if there was a back door if you have the right privileges -- but for application design, so application queries could use prepared plans without modifying the query to point to hard code the session information within them and be replanned. I'm not sure if they should use shared buffers or local buffers. As long as only one backend at a time could access them it would be possible to use local buffers and evict them all when the handle is given up. But that means giving up any caching benefit across sessions. On the other hand it means they'll be much lighter weight and easier to make safely unlogged than if they lived in shared buffers. These are just some brainstorming ideas, I don't have a clear vision of how to achieve all this yet. This does sound a lot like the SQL standard temp table discussion and I think Tom and I are still at odds on that. Creating new catalog entries for them gives up -- what I think is the whole point of their design -- their lack of DDL overhead. But my design above means problems for transactional TRUNCATE and other DDL. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
