Temporary Tables and Web Application

From: Tim Tassonis <timtas(at)cubic(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: Temporary Tables and Web Application
Date: 2008-06-05 15:36:28
Message-ID: 484807FC.3050309@cubic.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Tim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Collin Peters 2008-06-05 15:47:33 pgAdmin complains about vacuuming required after fresh 8.1 install
Previous Message Volkan YAZICI 2008-06-05 15:13:24 Re: does postgresql works on distributed systems?