På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost < sfr...@snowman.net <mailto:sfr...@snowman.net>>: Greetings,
* Melvin Davidson (melvin6...@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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
