Re: PostgreSQL 8.4 Tablespace Inconsistency

Harold Falkmeyer <hfalkmeyer(at)gmail(dot)com> writes:
> As an example of the seeming inconsistency, pg_class indicated that certain
> tables and indexes were on reltablespace 0, which, as we understood, refers
> to pg_default, which, coincidentally is listed with pg_tablespace with oid
> 1663. That same pg_class object is clearly present on the filesystem for a
> non-default tablespace.

No, reltablespace = 0 means that the relation is in its database's default
tablespace; that's whatever pg_database.dattablespace says, not
necessarily pg_default. The reason for this is basically to allow a
database to be moved en-masse to another tablespace without having
to update its pg_class.

> As another example, pg_class lists no tables or indexes with one of our
> non-default tablespace; though, that filesystem has a tablespace-like path
> with many open files (lsof) listed whenever the database is running.

Maybe those objects are in a different database of the cluster?

Another thought is to take a close look at the symlinks in
$PGDATA/pg_tblspc to verify that your tablespaces are pointing
where you think they are. Note that pg_tablespace.spclocation
is not authoritative on this; the symlinks are.

regards, tom lane