Re: Using the internal data dictionary

On Fri, Nov 18, 2011 at 10:54 AM, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:

> Thanks, guys!
>
> I'll take a closer look at the information_schema and pgAdmin and Maestro.
> Reinventing the wheel isn't a problem as this job is not critical, but the
> educational experience in looking at the system from another POV may be the
> bigger prize.
>
>
If you're looking to get to know the pg_catalog, run psql with the -E flag.
Every time you run a meta-command, you will get a SQL dump that shows you
how psql assembled the output:

scott$ bin/psql -E postgres
psql (9.2devel)
Type "help" for help.

postgres=# \dt+
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN
'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+------------+-------------
public | joe | table | scott | 8192 bytes |
public | mike | table | scott | 8192 bytes |
(2 rows)

postgres=#

It's a pretty quick way to get familiar with how the catalogs are
structured. I agree that when it comes to 'getting things done',
information_schema is quicker and more stable, but knowing pg_catalog is a
great way to step-it-up.

--
Scott Mead
OpenSCG, http://www.openscg.com

> - Bill
>
>
>
> On 11/17/2011 8:34 PM, David Johnston wrote:
>
> On Nov 17, 2011, at 22:17, Bill Thoen <bthoen(at)gisnet(dot)com> <bthoen(at)gisnet(dot)com> wrote:
>
>
> I need to assemble a complete data dictionary for project documentation and other purposes and I was wondering about the pros and cons of using the pg_catalog metadata. But I hesitate to poke around in here because I don't know why it's kept so out of sight and not much documented. But it seems like an ideal source of information to tap with a program to generate accurate, current reports of what's in the database.
>
> Is this a bad idea (everything I'm thinking of doing would be read only except for the description fields) but I'd just like to make sure that there's not some innocent looking table in there that acts as a doomsday device if you so much as read its first record, etc. I'm just not sure why this isn't more widely used or talked about.
>
> Regards,
>
> Bill Thoen
> GISnethttp://gisnet.com303-786-9961
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> information_schema is the more standardized point of entry into the database meta-data, catalog is generally intended for internals use and thus has a less stable API contract. That said, you are reinventing the wheel if you are looking for a straight dump of the current reality. Various third-party tools already do this. I've used, but am not affiliated with, PostgreSQL Maestro. Also, pgAdmin, I think, provides access to this information as well (as does psql via it's various commands).
>
> You should never directly update the catalog but instead use the appropriate SQL command. For descriptions you need to use "COMMENT ON". Reading it should never cause a problem.
>
> David J.
>
>
>
>