Hi, We're using PostgreSQL v8.2.3. Ours is a web-based application, language is Java and we're using pgpool-II v 2.0.1 purely for connection pooling (we don't use other features of pgpool like Replication, Load Balancing, etc.). I've an implementation-specific question related to creating of temporary table using CREATE TEMP TABLE syntax and either dropping of temporary TABLE or temporary SCHEMA before closing the database connection. I also understand and read from doc that "Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (using ON COMMIT)". Before end of session (in my case before closing database connection), I want to explicitly drop the table and its schema for the reason explained below. My question here is, which one is recommended and why: DROP TABLE pg_temp_xx.FOO or DROP SCHEMA pg_temp_xx We're running a complex report as explained below. Sometimes, the report is so huge and involves complex calculations that it may be take more than 2 hours to get a report. 1. Database connection is opened in Java with AutoCommit set to 'true' 2. Temporary table is created using "CREATE TEMP TABLE FOO(col1, col2, ...)" 3. INSERTs/UPDATEs are performed heavily to this temporary table FOO. Of course, this portion of logic is the one handling complex calculation, etc. Once everything is completed, finally all rows are deleted from this table. Hence, there will be zero rows at any point of time after completing this portion of logic. In fact, this INS/UPD are spanned across multiple PostgreSQL functions. 4. After building the report, database connection is closed. PostgreSQL server port: 5432 pgpool port: 9999 Now, we're facing a problem. Though we're logically closing connections from the application, it appears that those connections are still being reused by pgpool internally. Because it is being reused, session is still visible for next new database connection request, and eventually it is throwing ERROR: relation "foo" already exists. Instead If I try to run my application directly on PostgreSQL server port 5432, this error completely disappears. This problem we could consistently reproduce in 5432 and 9999 port. So, now we've decided to either DROP TABLE pg_temp_xx.FOO or DROP SCHEMA pg_temp_xx explicitly just before closing the connection (after step 3 and before step 4), so that it'll not be visible for the next database connection request. My question is, which one of the option is recommended and why? I can use the following query to find out the temporary schema name created in that session: select nspname from pg_namespace where oid = pg_my_temp_schema(); Experts advice/recommendation on the same are highly appreciated. Regards, Gnanam -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
