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