Re: PostgreSQL 8.4 Tablespace Inconsistency

On Fri, Aug 9, 2019 at 7:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Thank you. This clarification is extremely helpful.

As such, the following SQL now seems to produce results consistent with
what we would have expected:

SELECT
n.nspname||'.'||c.relname AS _relfqn,
c.oid,
c.relfilenode,
c.relkind,
t.spcname,
case when coalesce(t.spclocation,'') != '' then t.spclocation else
current_setting('data_directory') end AS _spclocation,
pg_relation_size(c.oid) AS _size
FROM
pg_class c
LEFT JOIN pg_database d ON ( d.datname = current_database() )
LEFT JOIN pg_namespace n ON ( c.relnamespace = n.oid )
LEFT JOIN pg_tablespace t ON ( case when
coalesce(c.reltablespace,0) != 0 then c.reltablespace else d.dattablespace
end = t.oid )
ORDER BY 1 ASC;

Also, it seems that \d examinations only show the specific tablespace when
not that of d.dattablespace!?

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

The objects were on the same cluster. We just had an inaccurate
understanding of pg_class.reltablespace and tablespace presentment with \d.

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

Another great point. We actually had done this and found that spclocation
was consistent with the symbolic links $PGDATA/pg_tblspc.

regards, tom lane
>

Thank you very much for your reply!

Appreciatively,

Harold