Search Postgresql Archives

Re: PostgreSQL 8.4 Tablespace Inconsistency

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux