[getting back to the original question]
"Gnanakumar" <gnanam@zoniac.com> wrote:
> 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"
> Though I'm dropping temporary tables explicitly just before
> closing database connection within my application, why this error
> is raised/reported?
Clearly it's trying to use an OID it calculated for one of these
tables after the table has been dropped, and I suspect that the lock
is released between gathering the data and sorting it. I don't have
any 8.2 databases around to try this on, but perhaps you would avoid
it with a slight rearrangement of your monitoring query:
SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname
||'.'||tablename)) AS size_pretty,
pg_size_pretty(pg_total_relation_size(schemaname
||'.'||tablename)) AS total_size_pretty
FROM pg_tables
where tablename ilike '%foo%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
;
If that doesn't do it I might try adding zero to numbers and
concatenating empty strings to try to prevent late use of the OID.
(Essentially as a form of optimization barrier.)
You could also try a more strict isolation level, like REPEATABLE
READ, but system table access often uses a special snapshot, so that
might not matter.
-Kevin
