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

PSQLException: ERROR: could not open relation with OID xxxx

From
"Gnanakumar"
Date:
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



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

From
"Kevin Grittner"
Date:
"Gnanakumar" <gnanam@zoniac.com> wrote:

> We're using PostgreSQL v8.2.3.

That version is over four years old, and there have been 17 minor
releases for 8.2 containing nothing except fixes for bugs and
security issues since then.  It's asking a lot to expect other
people to remember every single bug fixed over the last four years
and interpret how it might affect your problem.  Please read this
page:

http://www.postgresql.org/support/versioning

If you want to see whether any of the bugs fixed might be related to
your error messages, please read the release notes for 8.2.4 to
8.2.20 to see which might affect your situation:

http://www.postgresql.org/docs/8.2/static/release.html

Note that there is no need for a conversion to upgrade to a new
minor release, although you might need to rebuild some indexes.
(See the release notes.)

Also, consider that the 8.2 release is so old that it is scheduled
to go out of support entirely late this year:

http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

You should probably begin planning very soon for an upgrade to
version 9.0 or 9.1 sometime this year.

> 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.

Why in the world would you want to do that?  Have you considered
naming the temporary table differently, like maybe with a _tt suffix
to denote "temporary table"?

-Kevin

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

From
"Gnanakumar"
Date:
First, thanks for your advice.

> If you want to see whether any of the bugs fixed might be related to
> your error messages, please read the release notes for 8.2.4 to
> 8.2.20 to see which might affect your situation:

> http://www.postgresql.org/docs/8.2/static/release.html

I read all the release notes from 8.2.4 to 8.2.20, but I don't find anything
mentioned/indicated similar to my error message.

> Why in the world would you want to do that?  Have you considered
> naming the temporary table differently, like maybe with a _tt suffix
> to denote "temporary table"?

Well, let me explain the long story on why we've named temporary table same
as that of an existing permanent table, in short here.  We came to know the
advantage and feature of TEMPORARY tables only at later point of time after
completely developing all PostgreSQL functions (approx. 35 functions) for
running this particular complex report.  Also, the existing permanent table
is used for other reports as well in our application, but is not used to the
extent by which this complex report uses.  Instead of touching/refactoring
all the 35 functions just to rename the table name, we decided to create the
temporary table name same as that of existing permanent table for running
this complex report alone, so that those tables are automatically dropped at
the end of session and doesn't occupy any disk space also (including its
indexes and toast tables, if any).  Hope this makes clear.


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

From
Rosser Schwarz
Date:
On Sun, Mar 13, 2011 at 11:59 PM, Gnanakumar <gnanam@zoniac.com> wrote:

[...]

Although it's unrelated to your query about an exception being raised,
a word of advice on temporary tables.  (This may not be relevant to
your situation, but it's no small source of pain in one of the systems
I'm responsible for.)  Significant use of temporary tables will cause
commensurately significant bloat in your system catalogs.  We have a
system which, in the course of its "normal" operations, creates
hundreds of temporary tables for processing, aggregation, analysis,
and reporting.

Consequently, the database runs with significantly bloated system
catalogs, particularly pg_attribute, which, even with autovacuum
running, and tuned rather aggressively, as well as frequent scheduled
VACUUMs, reaches a steady-state of around 90% dead tuples.

This has adverse effects on query planning performance, among other
things.  (psql's tab-completion taking forever to complete is the one
that keeps biting me, because it works everywhere else, so I
constantly forget not to do it in this db.)  The only remedy is to
bring the application down, stop postgres and restart it in
single-user mode, and VACUUM FULL and REINDEX the affected system
catalogs.

I'm working with the development team to re-write the application to
use normal tables, which are TRUNCATEd after use, but until that can
be deployed, we're stuck with periodic downtime.

rls

--
:wq

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

From
"Gnanakumar"
Date:
> Although it's unrelated to your query about an exception being raised,
> a word of advice on temporary tables.  (This may not be relevant to
> your situation, but it's no small source of pain in one of the systems
> I'm responsible for.)  Significant use of temporary tables will cause
> commensurately significant bloat in your system catalogs.  We have a
> system which, in the course of its "normal" operations, creates
> hundreds of temporary tables for processing, aggregation, analysis,
> and reporting.

Thank you very much for sharing your real-time experience.

Do you think that this "significantly bloated system catalogs" is caused
because of the old version 8.2.3 we're running?  Will this be resolved in
latest PostgreSQL v9.0?  BTW, what version of PostgreSQL are you handling?

Doc says: " Temporary tables are automatically dropped at the end of a
session, or optionally at the end of the current transaction (see ON COMMIT
below). Any indexes created on a temporary table are automatically temporary
as well."
If temporary tables and its indexes are automatically dropped at the end of
the session, why would it cause bloat in system catalogs?  I hope you can
make me understand by sharing your real-time experience.

Again, thanks for your advice.


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

From
"Kevin Grittner"
Date:
"Gnanakumar" <gnanam@zoniac.com> wrote:

> Instead of touching/refactoring all the 35 functions just to
> rename the table name, we decided to create the temporary table
> name same as that of existing permanent table for running this
> complex report alone

If it were me I would be running some global search and replace on
the scripts which create those functions.  At a bare minimum you
should be running these reports under a login without rights to drop
permanent production tables.  (We never let applications connect
with a login which can do DDL except on temporary tables.)

-Kevin

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

From
"Kevin Grittner"
Date:
[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

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

From
Rosser Schwarz
Date:
(Apologies for thread-jacking; I saw something similar in the OP's
description of his setup that I thought warranted a word of advice.)

On Mon, Mar 14, 2011 at 1:50 AM, Gnanakumar <gnanam@zoniac.com> wrote:
> Do you think that this "significantly bloated system catalogs" is caused
> because of the old version 8.2.3 we're running?  Will this be resolved in
> latest PostgreSQL v9.0?  BTW, what version of PostgreSQL are you handling?

This isn't a version-specific issue; it's a consequence of MVCC.  (See
below for more details.)  We're using 8.3 for this application, but
we'd encounter the problem whatever version we were using.  I'm
working on getting it migrated to 8.4, but it's a multi-tbyte
database, and we don't currently have space available on the SAN for
an upgrade.

9.0 has a new VACUUM FULL which behaves more like CLUSTER.  That may
help to shorten the downtime windows we need to take, but it won't
eliminate them.  The only thing that will is changing the application
to make less pathological use of temporary tables.

> If temporary tables and its indexes are automatically dropped at the end of
> the session, why would it cause bloat in system catalogs?

The key to understanding the problem is to realize that system
catalogs are pretty much regular tables in PostgreSQL, with some
additional special rules (no triggers, can't be CLUSTERed or ALTERed,
&c).  Anything you'd do in a regular table that would create a dead
tuple — UPDATEs, DELETEs, &c — will have the same effect on a catalog.

A CREATE TABLE is an INSERT into pg_class for the table, plus one for
each of any indexes you may create, and so on.  It's also an INSERT
into pg_attribute for each column the table has, including the
"housekeeping" columns postgres uses, mostly to manage MVCC.  DROPping
a table is a DELETE from those catalogs, creating the corresponding
number of dead tuples.  Implicitly dropped temp tables still cause
dead tuples in the catalogs; it's just postgres dropping them for you
at commit time, rather than you issuing the DROP command explicitly.

If you're just using a few temp tables, you shouldn't be experiencing
the kinds of problems we are, particularly if you have autovacuum
enabled.  You'll have dead tuples, but they should quickly reach a
steady-state, where the space consumed by them is re-used by a later
creation of those temp tables.  Our situation is probably somewhat
unique, because of the sheer number of temp tables the application
uses.

I hope that helps you understand better.

rls

--
:wq

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

From
"Gnanakumar"
Date:
> The key to understanding the problem is to realize that system
> catalogs are pretty much regular tables in PostgreSQL, with some
> additional special rules (no triggers, can't be CLUSTERed or ALTERed,
> &c).  Anything you'd do in a regular table that would create a dead
> tuple — UPDATEs, DELETEs, &c — will have the same effect on a catalog.

Thanks for the detailed explanation on this topic.


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

From
"Gnanakumar"
Date:
> 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:

Thanks for both making this clear and providing an rearranged/modified
version of my original query.  I'll try out this option also.

> 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.)

I couldn't understand this approach clearly. Can you help explain me with
some example?


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

From
"Kevin Grittner"
Date:
"Gnanakumar" <gnanam@zoniac.com> wrote:

>> 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.)
>
> I couldn't understand this approach clearly. Can you help explain
> me with some example?

For a string it would be changing something like this:

    schemaname,

to something like this:

    schemaname || '' AS schemaname,

for a number it might be changing:

    pg_total_relation_size(schemaname||'.'||tablename)

to:

    pg_total_relation_size(schemaname||'.'||tablename) + 0

Again, I would try without these first.  If the problem persists I
would sprinkle these throughout the query.  If the problem goes
away, I would probably selectively remove them until the problem
returned, so I would know where it matters.

-Kevin