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

(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(at)zoniac(dot)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