Thread: using system catalogs

Hi:

I'm definitely a postgres newbie, and was wondering about how much can be done with the system
catalogs. I'd like to a create table that links up with the pg_database catalog so I can track more
info on each database (users, associated application, status, etc).

- I assume I cannot and should not attempt to add fields to pg_database

- I have tried to create a table that uses has a reference to pg_database (datname) which it
complains about. ERROR:  permission denied: "pg_database" is a system catalog_database(datname));

- I can create another table with a field that matches the datname in pg_database and try to write a
trigger that inserts into my table when a record is inserted into pg_database, but I have the feeling
that may also be
a dead end.

On a related note, I'm also trying to find the system table that links database oids with table or
schema oids, but I can't seem to find it. I know it's there somewhere, but don't see it in the doc.

thanks!
Mija

Mija Lee <mija@scharp.org> writes:
> - I can create another table with a field that matches the datname in
> pg_database and try to write a trigger that inserts into my table when
> a record is inserted into pg_database, but I have the feeling that may
> also be a dead end.

Yup, because we don't support firing triggers on system catalogs.
There are conceptual, security, and implementation reasons why not;
troll the pghackers archives for discussions.

> On a related note, I'm also trying to find the system table that links
> database oids with table or schema oids, but I can't seem to find
> it. I know it's there somewhere, but don't see it in the doc.

You don't see it because it's not there.  pg_class and so forth live
within a database, that is, there's a physically separate version in
each database; so the database ID is implicit.  There are actually only
a few system catalogs that don't work that way --- they are the ones we
call "shared".

            regards, tom lane

On Aug 29, 2007, at 13:26 , Mija Lee wrote:

> I'm definitely a postgres newbie, and was wondering about how much
> can be done with the system
> catalogs. I'd like to a create table that links up with the
> pg_database catalog so I can track more
> info on each database (users, associated application, status, etc).

Have you looked a the information schema? Does that provide views to
information you need? If there's something more specific, you can
always write views to gather this information for you, possibly
storing some additional information in other tables, as you've
alluded to below.

> - I assume I cannot and should not attempt to add fields to
> pg_database

Yup.

> - I have tried to create a table that uses has a reference to
> pg_database (datname) which it
> complains about. ERROR:  permission denied: "pg_database" is a
> system catalog_database(datname));

Yup.

> - I can create another table with a field that matches the datname
> in pg_database and try to write a
> trigger that inserts into my table when a record is inserted into
> pg_database, but I have the feeling
> that may also be
> a dead end.

You might also want to look at client apps like pgAdmin III: I'm not
familiar with them, but they may provide convenient views into the
information you want.

> On a related note, I'm also trying to find the system table that
> links database oids with table or
> schema oids, but I can't seem to find it. I know it's there
> somewhere, but don't see it in the doc.

It sounds like you've been looking at the system catalog
documentation, but I think you should look pg_database, pg_namespace,
and pg_class in more depth. I think they provide the information
you're looking for.

Michael Glaesemann
grzm seespotcode net