Melvin, * Melvin Davidson ([email protected]) wrote: > On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <[email protected]> wrote: > > * Melvin Davidson ([email protected]) wrote: > > > >There should be a "catalog" that point where tables are stored in > > physical > > > files... > > > > > > Here is the query that gives you that information. > > > > > > SELECT c.oid, > > > n.nspname as schema, > > > c.relname as table, > > > (SELECT oid FROM pg_database WHERE datname = > > > current_database() ) as db_dir, > > > c.relfilenode as filename > > > FROM pg_class c > > > JOIN pg_namespace n ON (n.oid = c.relnamespace) > > > WHERE relname NOT LIKE 'pg_%' > > > AND relname NOT LIKE 'information%' > > > AND relname NOT LIKE 'sql_%' > > > AND relkind = 'r' > > > ORDER BY 2, relname; > > > > This isn't a terribly good query- it's entirely valid to have 'pg_blah' > > and 'informationblah' tables in user schemas. If you'd like to filter > > out the catalogs/internal schemas, filter based on schema name instead. > > > > Also, this doesn't actually provide what Edson is asking for. Edson's > > asking for a query that uses pg_ls_dir() or some custom function which > > will run 'stat' on each file and return the size, according to the OS. > > *Edson's original request was for a query that shows the FILENAMEs for the > table.*
As quoted previously, he request included:
---
> Based on information from this catalog, would I have a tool (perhaps, a C
>
> function) that check that data is really there?
---
Which is asking about having a function to 'stat' the files and check
their length ('data is really there').
> *As for "qood" query, that is entirely an opinion. The query WILL show all
> files associated*
> *with ALL tables. You are free to edit and reconstruct as you choose. *
No, it won't, it'll filter out tables which exist in user schemas that
happen to start with one of the strings that the query includes ('pg_',
'information', and 'sql_').
I encourage you to test it- create a table in the public schema called
'pg_whatever' and see if your query picks it up or not. This isn't a
minor complaint about style, the query is outright wrong.
Thanks!
Stephen
signature.asc
Description: PGP signature
