On Fri, Aug 9, 2019 at 7:15 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Harold Falkmeyer <hfalkmeyer@xxxxxxxxx> 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,
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;
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
