Re: Temporary Tables and Web Application

In response to Tim Tassonis <timtas(at)cubic(dot)ch>:

> Hi all
>
> I assume this is not an uncommon problem, but so far, I haven't been
> able to find a good answer to it.
>
> I've got a table that holds log entries and fills up very fast during
> the day, it gets approx. 25 million rows per day. I'm now building a web
> application using apache/mod_php where you can query the database and
> then should be able to page through the results.
>
> My idea was that whenever a user constructs a query, I create a
> temporary table holding the results and then page through this table,
> which should work very well in principle.
>
> But from what I've been able to find out, temporary tables live only in
> the Postgres Session they have been created in and are destroyed upon
> session descructuion.
>
> 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.

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

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