Search Postgresql Archives

Re: Does PostgreSQL check database integrity at startup?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Melvin,

* Melvin Davidson (melvin6925@xxxxxxxxx) wrote:
> On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:
> > * Melvin Davidson (melvin6925@xxxxxxxxx) 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

Attachment: signature.asc
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux