På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost < sfr...@snowman.net <mailto:sfr...@snowman.net>>: * Andreas Joseph Krogh (andr...@visena.com) wrote: > 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:
Nothing in this query referred to types, so I'm not sure what custom types would have to do with it..? > 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 │ Considering this is saaying 'no such file or directory', I'm guessing that somehow your data directory isn't what is listed in pg_settings..? Alternatively, perhaps that table was concurrently dropped? Are you able to provide any specifics about your system? Does the database directory exist? Does that path look reasonable? I find it kind of interesting that the OID of the database and the relfilenode are so close together- exactly what did you do to test this query? 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); 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 <http://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'; ┌─────────────────────┬──────────────────────────────────────────────────────────────────┐ │ ?column? │ ?column? │ ├─────────────────────┼──────────────────────────────────────────────────────────────────┤ │ public.foo_id_seq │ /home/andreak/programs/postgresql-10/data/base/22058766/22058767 │ │ public.foo │ /home/andreak/programs/postgresql-10/data/base/22058766/22058769 │ │ public.foo_pkey │ /home/andreak/programs/postgresql-10/data/base/22058766/22058776 │ │ public.biginttuple2 │ /home/andreak/programs/postgresql-10/data/base/22058766/22058778 │ └─────────────────────┴──────────────────────────────────────────────────────────────────┘ 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 <http://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/22058766/22058778": No such file or directory $ file /home/andreak/programs/postgresql-10/data/base/22058766/22058776 /home/andreak/programs/postgresql-10/data/base/22058766/22058776: lif file -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
