Re: Number of tables

On Fri, Aug 21, 2009 at 1:38 AM, Alvaro
Herrera<alvherre(at)commandprompt(dot)com> 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