Search code examples
sqlpostgresqlluatemporary

creating postgresql temporary tables for search / reporting routine

Background Information

We have some lua code that generates a web report. It's taking a really long time right now and so in attempt to simplify some of the logic, I'm looking at creating a temporary table, and then joining that temp table with the results of another query.

Sample Code:

I tried the following as a test on the commandline: 

psql -U username -d databasename
DROP TABLE IF EXISTS TEMP1;
CREATE TABLE TEMP1 AS SELECT d_id, name as group, pname as param
FROM widgets
WHERE widget_id < 50;

SELECT count(*) from TEMP1;

\q

The select against the TEMP1 table show the correct results.

Questions:

Question 1 - How do I code this to ensure that one report request doesn't clobber another? For example, if person A requests report A and before it's done processing, person B request report B... Will report B's creation of TEMP1 clobber the temp table created for report A? Is this a good reason to put everything into a transaction?

Question 2 - After running my little test described above, I quit postgresql command line.... and then logged in again. TEMP1 was still around. So it looks like I have to clean up the temp table when I'm done. I found this post: PostgreSQL temporary tables

which seems to indicate that temp tables are cleaned up for you when a session ends... but that doesn't seem to be working for me. Not sure what I'm doing wrong.

Thanks.

Solution

  • Just use:

    CREATE TEMPORARY TABLE temp1 AS ...

    This solves both questions #1 and #2 because:

    1. Temporary tables live in a namespace that's private to the session, so when concurrent sessions use the same name for a temporary table, it refers to different tables, each session its own table.

    2. TEMP1 was still around after quitting because it's not temporary. You want to add the TEMPORARY clause (or TEMP for short) to the CREATE TABLE statement.