Thread: Inspecting a DB - psql or system tables ?

Suppose you want to write a program that connects to a
PostgreSQL database and lists its tables and views, the type of
their columns and their relationships (REFERENCES) for automatic
joins.

Would you look at the system tables (pg_class et al.) or the
output of psql \d, \dt, etc ?

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.

--
André Majorel http://www.teaser.fr/~amajorel/

Andre Majorel <aym-2lqsgp <at> teaser.fr> writes:

>
> Suppose you want to write a program that connects to a
> PostgreSQL database and lists its tables and views, the type of
> their columns and their relationships (REFERENCES) for automatic
> joins.

I personally would try to hit the "informat_schema", which is a bunch of views
on system activity that follows a standard. I am not sure if what you want is in
there, like locks or other stuff, but for table names etc it works well.

Dne 27.5.2011 19:24, Andre Majorel napsal(a):
> Suppose you want to write a program that connects to a
> PostgreSQL database and lists its tables and views, the type of
> their columns and their relationships (REFERENCES) for automatic
> joins.
>
> Would you look at the system tables (pg_class et al.) or the
> output of psql \d, \dt, etc ?

System tables (old-fashioned pg_ catalogs or information_schema). Psql
reads those catalogs anyway, so parsing the output seems like an
unnecessary step.

> 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.

regards
Tomas

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.)

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 05/27/2011 01:24 PM, Andre Majorel 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.
>

I think you have this backwards.  If there's a change in this area big
enough to justify changing the format of the system tables, odds are the
text output from psql is going to be changed too.  psql gets tweaked to
display information better more often than the internals are altered.

Approaches you can take here, from most robust in the face of changes to
most fragile, in my mind are:

1) Use information_schema.  If all the info you need is in here, great;
it may not be though.
2) Use the system catalog data directly
3) Parse text output from psql.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Andrew Sullivan
> Sent: Friday, May 27, 2011 2:32 PM
> To: pgsql-general@postgresql.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.