Re: PSQLException: ERROR: could not open relation with OID xxxx

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: PSQLException: ERROR: could not open relation with OID xxxx
Дата
Msg-id 4D7DEB6B020000250003B821@gw.wicourts.gov
обсуждение исходный текст
Ответ на PSQLException: ERROR: could not open relation with OID xxxx  ("Gnanakumar" <gnanam@zoniac.com>)
Ответы Re: PSQLException: ERROR: could not open relation with OID xxxx  ("Gnanakumar" <gnanam@zoniac.com>)
Список pgsql-admin
[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

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: PSQLException: ERROR: could not open relation with OID xxxx
Следующее
От: Rosser Schwarz
Дата:
Сообщение: Re: PSQLException: ERROR: could not open relation with OID xxxx