* Andreas Joseph Krogh (andreas@xxxxxxxxxx) 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? Thanks! Stephen
Attachment:
signature.asc
Description: PGP signature
