Re: Does PostgreSQL check database integrity at startup?

>the query is outright wrong.
Really? I submit a query to help and all you can do is criticize?
Yes it is legal to create a table that starts with pg_, but any PostgreSQL
developer should
know that is not a good idea.

If you don't like the query, write a better one yourself, but
kindly do not try to degrade the efforts of others.

On Fri, Dec 29, 2017 at 9:22 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Melvin,
>
> * Melvin Davidson (melvin6925(at)gmail(dot)com) wrote:
> > On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> > > * Melvin Davidson (melvin6925(at)gmail(dot)com) 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
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.