Re: Sv: Re: Sv: Re: Does PostgreSQL check database integrity atstartup? - Mailing list pgsql-general

Greetings Andreas,

* Andreas Joseph Krogh (andreas@visena.com) wrote:
> Here's a simple test-case:
>  
> createdb test
> test=# create table foo(id serial primary key, name varchar not null);
> test=# CREATE TYPE BigIntTuple2 AS (f1 bigint, f2 bigint);

Hah, appears we allocate a relfilenode to types too, though it's not
entirely clear to me why (evidently, they're not used..).

Anyhow, adding a filter on relkind addresses it (though it's not very
future-proof, unfortunately):

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size as size
FROM
  pg_settings s
  JOIN pg_database db on (s.name = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema'
  AND c.relkind in ('r','i','m','p');

Thanks!

Stephen