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.).
We're running a complex report as explained below. Sometimes, the report is
so huge and involves complex calculations that it may 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. Temporary table is dropped using "DROP TABLE pg_temp_xx.FOO". Since
DISCARD is available only from 8.3+, I couldn't use it here. So only choice
left out for us here is to drop the temporary table explicitly. We're
creating temporary table with the same as an existing permanent table which
is available in "public" schema, that is FOO. Hence, we always qualify with
temporary schema name while dropping the table. We're using the following
query to find out the temporary schema name created in that session, so that
this can be used in DROP TABLE:
select nspname from pg_namespace where oid = pg_my_temp_schema();
5. After building the report, database connection is closed.
Now, in order to monitor the growth (table size) of temporary tables created
while report is running, we've a "separate Java standalone program" which
automatically executes the following query every 5 minutes and keeps writing
output to a separate log file, which will helps us to analyze the size
occupied by each temporary table in detail at later point of time:
SELECT schemaname, tablename, pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size FROM
pg_tables where tablename ilike '%foo%') AS TABLES ORDER BY total_size DESC
Sometimes, the following PSQLException is thrown by the above query. As I
said, this error is thrown only sometime and not always.
"org.postgresql.util.PSQLException: ERROR: could not open relation with OID
1034158"
My questions are:
1) Though I'm dropping temporary tables explicitly just before closing
database connection within my application, why this error is
raised/reported?
2) In above query, am using the function which accepts
"schemaname.tablename" as its parameter "pg_relation_size(text)" and not the
function which accepts oid as its parameter "pg_relation_size(oid)". The
error "could not open relation with OID" should only be raised when
"pg_relation_size(oid)" is used, but why this error is raised here. I even
tried to execute the following queries from "psql", by passing both table
name and oid that do not exist in my database and am getting the proper
error message:
select pg_total_relation_size('pg_temp_25.nonexistence');
ERROR: relation "pg_temp_25.nonexistence" does not exist
select pg_total_relation_size(12345);
ERROR: could not open relation with OID 12345
Regards,
Gnanam
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
