Re: Elementary dependency look-up

Attached is a patch to add a couple basic dependency look-up capability
functions. They're based off the pg_get_serial_sequence function, and
are kind of the inverse of that function in some respects.

The patch adds two new functions to the backend, pg_get_owner_object and
pg_get_owner_column. These look up the requested object in the
pg_depend table, looking for an 'a' type dependency to another relation,
and resolve either the relation or column names to text.

postgres=# SELECT pg_get_owner_object('tbl_id_seq') AS obj,
postgres-# pg_get_owner_column('tbl_id_seq') AS col,
postgres-# pg_get_serial_sequence(
postgres(# pg_get_owner_object('tbl_id_seq'),
postgres(# pg_get_owner_column('tbl_id_seq')
postgres(# ) AS full_circle;
obj | col | full_circle
------------+-----+-------------------
public.tbl | id | public.tbl_id_seq
(1 row)

I tried not to be too myopic in the design, but apart from sequence
ownership I can't really think of any other uses for this. 'p'in and
'i'nternal relationships wouldn't make much sense, and 'n'ormal ones are
generally exposed in other ways. Anyone have any input there on how
this could be expanded?

Anyway, as an immediate practical example the patch modifies psql's
describe-verbose on sequences to show the ownership information...

postgres=# \d+ tbl_id_seq
(...)
Owner: public.tbl.id

- Josh Williams

On Thu, 2009-09-03 at 20:45 -0400, Josh Williams wrote:
> The patch adds two new functions to the backend, pg_get_owner_object
> and
> pg_get_owner_column. These look up the requested object in the
> pg_depend table, looking for an 'a' type dependency to another
> relation,
> and resolve either the relation or column names to text.

How is this better than just reading the information directly from
pg_depend?

On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
> On Thu, 2009-09-03 at 20:45 -0400, Josh Williams wrote:
>> The patch adds two new functions to the backend, pg_get_owner_object
>> and
>> pg_get_owner_column. These look up the requested object in the
>> pg_depend table, looking for an 'a' type dependency to another
>> relation,
>> and resolve either the relation or column names to text.
>
> How is this better than just reading the information directly from
> pg_depend?

pg_depend is very difficult to use. You have to really, really know
the catalogs to be able to figure it out. Part of the problem is
(afaik) there's nothing that documents every kind of record/
dependency you might find in there.

What might be more useful is a view that takes the guesswork out of
using pg_depend. Namely, convert (ref)classid into a catalog table
name (or better yet, what type of object it is), (ref)objid into an
actual object name, and (ref)objsubid into a real name.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Wed, 2009-09-09 at 11:30 -0500, decibel wrote:
> On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
> > How is this better than just reading the information directly from
> > pg_depend?
>
> pg_depend is very difficult to use. You have to really, really know
> the catalogs to be able to figure it out. Part of the problem is
> (afaik) there's nothing that documents every kind of record/
> dependency you might find in there.

Exactly - these functions were designed around making that easier for
the end user. The less poking around in system catalogs a user has to
do the better.

Yeah, the documentation about what can be found in pg_depend is
scattered at best, though then again there doesn't seem to be a whole
lot in there that's of much interest to end users... Actually, apart
from pg_get_serial_sequence() do we have anything else that utilizes
dependency data to show the user information?

> What might be more useful is a view that takes the guesswork out of
> using pg_depend. Namely, convert (ref)classid into a catalog table
> name (or better yet, what type of object it is), (ref)objid into an
> actual object name, and (ref)objsubid into a real name.

Makes sense, would be much more future-proof. It shouldn't be difficult
to put in some intelligence to figure out the type of object, such as
looking at relkind if (ref)classid = pg_class.

It might be a little difficult to maintain, depending on what else finds
its way into the system catalogs later (but then, probably not much more
so than INFORMATION SCHEMA is.) Would that be preferable, over a couple
additional functions?

- Josh Williams

On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams <joshwilliams(at)ij(dot)net> wrote:
> On Wed, 2009-09-09 at 11:30 -0500, decibel wrote:
>> On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
>> > How is this better than just reading the information directly from
>> > pg_depend?
>>
>> pg_depend is very difficult to use. You have to really, really know
>> the catalogs to be able to figure it out. Part of the problem is
>> (afaik) there's nothing that documents every kind of record/
>> dependency you might find in there.
>
> Exactly - these functions were designed around making that easier for
> the end user.  The less poking around in system catalogs a user has to
> do the better.
>
> Yeah, the documentation about what can be found in pg_depend is
> scattered at best, though then again there doesn't seem to be a whole
> lot in there that's of much interest to end users...  Actually, apart
> from pg_get_serial_sequence() do we have anything else that utilizes
> dependency data to show the user information?
>
>> What might be more useful is a view that takes the guesswork out of
>> using pg_depend. Namely, convert (ref)classid into a catalog table
>> name (or better yet, what type of object it is), (ref)objid into an
>> actual object name, and (ref)objsubid into a real name.
>
> Makes sense, would be much more future-proof.  It shouldn't be difficult
> to put in some intelligence to figure out the type of object, such as
> looking at relkind if (ref)classid = pg_class.
>
> It might be a little difficult to maintain, depending on what else finds
> its way into the system catalogs later (but then, probably not much more
> so than INFORMATION SCHEMA is.)  Would that be preferable, over a couple
> additional functions?

+1.

...Robert

On Thu, Sep 10, 2009 at 10:23 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams <joshwilliams(at)ij(dot)net> wrote:
>> On Wed, 2009-09-09 at 11:30 -0500, decibel wrote:
>>> On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
>>> > How is this better than just reading the information directly from
>>> > pg_depend?
>>>
>>> pg_depend is very difficult to use. You have to really, really know
>>> the catalogs to be able to figure it out. Part of the problem is
>>> (afaik) there's nothing that documents every kind of record/
>>> dependency you might find in there.
>>
>> Exactly - these functions were designed around making that easier for
>> the end user.  The less poking around in system catalogs a user has to
>> do the better.
>>
>> Yeah, the documentation about what can be found in pg_depend is
>> scattered at best, though then again there doesn't seem to be a whole
>> lot in there that's of much interest to end users...  Actually, apart
>> from pg_get_serial_sequence() do we have anything else that utilizes
>> dependency data to show the user information?
>>
>>> What might be more useful is a view that takes the guesswork out of
>>> using pg_depend. Namely, convert (ref)classid into a catalog table
>>> name (or better yet, what type of object it is), (ref)objid into an
>>> actual object name, and (ref)objsubid into a real name.
>>
>> Makes sense, would be much more future-proof.  It shouldn't be difficult
>> to put in some intelligence to figure out the type of object, such as
>> looking at relkind if (ref)classid = pg_class.
>>
>> It might be a little difficult to maintain, depending on what else finds
>> its way into the system catalogs later (but then, probably not much more
>> so than INFORMATION SCHEMA is.)  Would that be preferable, over a couple
>> additional functions?
>
> +1.

I'm not sure there's any point in reviewing this patch in its present
form. Barring objections (or a new version), I think we should mark
this Returned with Feedback.

...Robert

On Sun, 2009-09-13 at 21:20 -0400, Robert Haas wrote:
> I'm not sure there's any point in reviewing this patch in its present
> form. Barring objections (or a new version), I think we should mark
> this Returned with Feedback.
>
> ...Robert

Yeah, sounds reasonable. The new version probably won't look at all
like the current one, so no need to waste reviewer cycles on it.

I'll work on a revised version; feel free to mark it as such in the mean
time. Thanks,

- Josh Williams

On Wed, 9 Sep 2009, decibel wrote:

> What might be more useful is a view that takes the guesswork out of using
> pg_depend. Namely, convert (ref)classid into a catalog table name (or better
> yet, what type of object it is), (ref)objid into an actual object name, and
> (ref)objsubid into a real name.

Here's V1 of a depend unraveler I needed recently and that's saved me a
bunch of time:

SELECT
c1.oid as relid,
n1.nspname || '.' || c1.relname as relation,
c1.relkind,
CASE
WHEN c1.relkind='r' THEN 'table'
WHEN c1.relkind='i' THEN 'index'
WHEN c1.relkind='S' THEN 'sequence'
WHEN c1.relkind='v' THEN 'view'
WHEN c1.relkind='c' THEN 'composite'
WHEN c1.relkind='t' THEN 'TOAST'
ELSE '?'
END as "kind",
c2.oid as relid,
n2.nspname || '.' || c2.relname as dependency,
c2.relkind,
CASE
WHEN c2.relkind='r' THEN 'table'
WHEN c2.relkind='i' THEN 'index'
WHEN c2.relkind='S' THEN 'sequence'
WHEN c2.relkind='v' THEN 'view'
WHEN c2.relkind='c' THEN 'composite'
WHEN c2.relkind='t' THEN 'TOAST'
ELSE '?'
END as "kind"
FROM
pg_depend d,
pg_class c1,
pg_namespace n1,
pg_class c2,
pg_namespace n2
WHERE
d.objid = c1.oid AND
c1.relnamespace = n1.oid AND
n1.nspname NOT IN('information_schema', 'pg_catalog') AND
n1.nspname !~ '^pg_toast' AND

d.refobjid = c2.oid AND
c2.relnamespace = n2.oid AND
n2.nspname NOT IN('information_schema', 'pg_catalog') AND
n2.nspname !~ '^pg_toast' AND

c1.oid != c2.oid

GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
n2.nspname,c2.relname,c2.oid,c2.relkind
ORDER BY n1.nspname,c1.relname;

I could throw this on the Wiki as a code snippet if anyone else wanted to
tinker with it.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sep 14, 2009, at 1:36 AM, Greg Smith wrote:
> CASE
> WHEN c1.relkind='r' THEN 'table'
> WHEN c1.relkind='i' THEN 'index'
> WHEN c1.relkind='S' THEN 'sequence'
> WHEN c1.relkind='v' THEN 'view'
> WHEN c1.relkind='c' THEN 'composite'
> WHEN c1.relkind='t' THEN 'TOAST'
> ELSE '?'
> END as "kind",

I think part of this patch should be providing a function or
something that converts things like pg_class.relkind into a useful
string. I know I've created a function that does that (though, I
return a cased string, since it's easier to run it through lower than
to try and case it after the fact). I'm not sure if a function is the
best way to do this or if a table or view would be better (something
you could join to). One benefit of a table or view is that you could
provide both cased and lower versions of the names.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Added to TODO:

|Add function to dump pg_depend information cleanly

---------------------------------------------------------------------------

Greg Smith wrote:
> On Wed, 9 Sep 2009, decibel wrote:
>
> > What might be more useful is a view that takes the guesswork out of using
> > pg_depend. Namely, convert (ref)classid into a catalog table name (or better
> > yet, what type of object it is), (ref)objid into an actual object name, and
> > (ref)objsubid into a real name.
>
> Here's V1 of a depend unraveler I needed recently and that's saved me a
> bunch of time:
>
> SELECT
> c1.oid as relid,
> n1.nspname || '.' || c1.relname as relation,
> c1.relkind,
> CASE
> WHEN c1.relkind='r' THEN 'table'
> WHEN c1.relkind='i' THEN 'index'
> WHEN c1.relkind='S' THEN 'sequence'
> WHEN c1.relkind='v' THEN 'view'
> WHEN c1.relkind='c' THEN 'composite'
> WHEN c1.relkind='t' THEN 'TOAST'
> ELSE '?'
> END as "kind",
> c2.oid as relid,
> n2.nspname || '.' || c2.relname as dependency,
> c2.relkind,
> CASE
> WHEN c2.relkind='r' THEN 'table'
> WHEN c2.relkind='i' THEN 'index'
> WHEN c2.relkind='S' THEN 'sequence'
> WHEN c2.relkind='v' THEN 'view'
> WHEN c2.relkind='c' THEN 'composite'
> WHEN c2.relkind='t' THEN 'TOAST'
> ELSE '?'
> END as "kind"
> FROM
> pg_depend d,
> pg_class c1,
> pg_namespace n1,
> pg_class c2,
> pg_namespace n2
> WHERE
> d.objid = c1.oid AND
> c1.relnamespace = n1.oid AND
> n1.nspname NOT IN('information_schema', 'pg_catalog') AND
> n1.nspname !~ '^pg_toast' AND
>
> d.refobjid = c2.oid AND
> c2.relnamespace = n2.oid AND
> n2.nspname NOT IN('information_schema', 'pg_catalog') AND
> n2.nspname !~ '^pg_toast' AND
>
> c1.oid != c2.oid
>
> GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
> n2.nspname,c2.relname,c2.oid,c2.relkind
> ORDER BY n1.nspname,c1.relname;
>
> I could throw this on the Wiki as a code snippet if anyone else wanted to
> tinker with it.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +