From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Sv: Re: Does PostgreSQL check database integrity at startup? |
Date: | 2017-12-30 23:43:40 |
Message-ID: | VisenaEmail.b.f4d213c52c37978f.160a9ce3410@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost <
sfrost(at)snowman(dot)net <mailto:sfrost(at)snowman(dot)net>>:
Greetings,
* Melvin Davidson (melvin6925(at)gmail(dot)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(at)[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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2017-12-30 23:49:31 | Re: Sv: Re: Does PostgreSQL check database integrity at startup? |
Previous Message | Stephen Frost | 2017-12-30 22:06:52 | Re: Does PostgreSQL check database integrity at startup? |