Re: Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

Greetings Andreas,

* Andreas Joseph Krogh (andreas(at)visena(dot)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