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

From Andreas Joseph Krogh
Subject Sv: Re: Does PostgreSQL check database integrity at startup?
Date
Msg-id VisenaEmail.b.f4d213c52c37978f.160a9ce3410@tc7-visena
Whole thread Raw
In response to Re: Does PostgreSQL check database integrity at startup?  (Stephen Frost <sfrost@snowman.net>)
Responses Re: Sv: Re: Does PostgreSQL check database integrity at startup?
List pgsql-general
På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost <sfrost@snowman.net>:
Greetings,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> My query works as designed and has done so
> for two years. It shows the filenames for schemas and tables in the
> database.

I'm glad to hear that it works in your specific use-case.
Unfortunately, it doesn't work in the general case and therefore isn't a
good example.  A proper query to return the filename for each user table
in the current database is:

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode
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';

Note that, as discussed earlier in this thread, this doesn't actually
answer what Edson was asking for.  Here's the query that would answer
his original request:

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';

Technically speaking, while these queries are correct for PG10, in prior
versions of PostgreSQL it's possible to have user schemas that begin
with 'pg_' and therefore the filtering in the WHERE clause would have to
be more specific.

Note that both of these need to be run as a superuser in older versions
of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
and be able to run the first query.  We don't currently support being
able to GRANT a non-superuser the ability to run pg_stat_file(), but
that will likely be coming in PG 11.

Thanks!
 
That doesn't seem to work with custom types:
 
andreak@[local]:5433 10.1 andreak=# 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';
ERROR:  could not stat file "/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such file or directory

 
 
│ public.biginttuple2 │ /home/andreak/programs/postgresql-10/data/base/22039391/22039392 │
 
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-general by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Does PostgreSQL check database integrity at startup?
Next
From: Stephen Frost
Date:
Subject: Re: Sv: Re: Does PostgreSQL check database integrity at startup?