Thread: Using temp tables with web apps (No State)

I have inherited a web application that uses a lot of temporary tables.
I have modified some of the code so that the tables are truly postgres
temp tables. But the temporary tables that need to exist between web
accesses cannot be temp tables, or the second access fails.
(Example. A query that returns 100 rows, and the web user sees 10
at a time)  I am wondering if anyone has a decent method of cleaning up
these temporary tables after use.
I am considering creating a table called Temp_Tables, and inserting the
name of the temp_tables into it, and having a cron job read Temp_Tables
for a list of temp_tables to drop. Any other suggestions are appreciated.

--
Danny Aldham     Providing Certified Internetworking Solutions to Business
www.postino.com  E-Mail, Web Servers, Web Databases, SQL PHP & Perl

Danny Aldham <danny@lennon.postino.com> writes:
> I am wondering if anyone has a decent method of cleaning up
> these temporary tables after use.
> I am considering creating a table called Temp_Tables, and inserting the
> name of the temp_tables into it, and having a cron job read Temp_Tables
> for a list of temp_tables to drop. Any other suggestions are appreciated.

Are you able to choose the table names freely?  If so, consider encoding
the needed info right into the table name:

    myapp_temp_200111301003_1234

(I'm imagining the current timestamp and PID as the variable components
here, but you could alter that to suit your needs.)  Then a scan of
pg_class tells the cron job what to do.  This avoids the overhead of an
extra table insert/delete, and more importantly avoids any possibility of
the Temp_Tables table getting out of sync with reality.

            regards, tom lane