Re: Temporary Tables and Web Application

Hi Tim,

Off the top of my head, from somewhat left field, using filesystems to manage this sort of effect.

Would "real" tables in a tablespace defined on a ramdisk meet this need? So the functionality/accessibility of a
physical table is provided, along with the performance of a filesystem actually residing in memory. Presumeably viable if you have the memory to spare & know the size of the temp tables won't exceed this.

You could also mount a tablespace on a physical disk with a filesystem which has delayed/deferred writes to disk, so that if it is created & deleted quickly enough, it is never actually written to disk, but just generally sits in the cache.

Cheers,

Brent Wood

>>> Bill Moran <wmoran(at)collaborativefusion(dot)com> 06/06/08 8:01 AM >>>
In response to Tim Tassonis <timtas(at)cubic(dot)ch>:
>
> Bill Moran wrote:
> > In response to Tim Tassonis <timtas(at)cubic(dot)ch>:
> >
> >>
> >> Now, with apache/php in a mpm environment, I have no guarantee that a
> >> user will get the same postgresql session for a subsequent request, thus
> >> he will not see the temporary table.
> >>
> >> Is there a way to create temporary tables in another way, so they are
> >> visible between sessions, or do I need to create real tables for my
> >> purpose? And is the perfomance penalty big for real tables, as they have
> >> been written to disk/read from disk?
> >
> > Build a framework that creates the tables in a special schema, and then
> > can access them through any session. Use some method to generate unique
> > table names and store the names in the HTTP session. Create some sort
> > of garbage collection routines that removes tables when they're no longer
> > needed.
> >
> > The details of exactly how you pull this off are going to depend heavily
> > on the rest of your application architecture.
> >
>
> What you describe is what I referred to as "create real tables". I've
> done that and it works, but I wondered if there's something similar
> built in postgres apart from classical temporary tables.

Not that I'm aware of.

If you keep the mailing list in the CC, others can answer as well.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023