Thread: 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
Attachment
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@decibel.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@ij.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@gmail.com> wrote: > On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams <joshwilliams@ij.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'
WHENc1.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@gregsmith.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@decibel.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@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.comPG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard
drive,Christ can be your backup. +