Re: Inspecting a DB - psql or system tables ?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: Friday, May 27, 2011 2:32 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Inspecting a DB - psql or system tables ?
>
> On Fri, May 27, 2011 at 08:26:33PM +0200, Tomas Vondra wrote:
> > > While parsing the output of psql is cumbersome, accessing the
> > > system tables seems more likely to break whenever a new version
> > > of PostgreSQL comes out.
> >
> > Really? Those catalogs are pretty stable, and when changed they're
> > usually extended (new columns are added). So well written queries
> won't
> > break very often. Actually I'd expect the psql output to change much
> > more often.
>
> The whole point of the information_schema is that it's well-defined by
> the standard. The system tables themselves do sometimes change
> between versions -- that's why you get warnings from psql when you
> start up a client with a different major version number than the
> server. (If you want to see this in action, try using a 7.4-era
> client with 9.0, and do some tab completion or something like that.)
>

There is a sharp edge to watch out for when querying for this data between the system catalogs and the information schema, and it's not mentioned in our docs anywhere.

The information schema queries will only return rows back for objects that the user issuing the query has permissions on. This is the correct behavior as per the SQL spec I believe, but very different from the way the pg_catalog queries work - which will return you all objects back regardless of permissions on them.

Brad.