Re: [HACKERS] Elementary dependency look-up
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 gsm...@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 br...@momjian.ushttp://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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
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 gsm...@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
Re: [HACKERS] Elementary dependency look-up
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 deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
On Thu, Sep 10, 2009 at 10:23 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams joshwilli...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams joshwilli...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
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 deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Elementary dependency look-up
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.487
diff -c -r1.487 func.sgml
*** doc/src/sgml/func.sgml 16 Aug 2009 19:55:21 - 1.487
--- doc/src/sgml/func.sgml 2 Sep 2009 23:11:15 -
***
*** 12264,12269
--- 12264,12277
/indexterm
indexterm
+ primarypg_get_owner_object/primary
+/indexterm
+
+indexterm
+ primarypg_get_owner_column/primary
+/indexterm
+
+indexterm
primarypg_tablespace_databases/primary
/indexterm
***
*** 12365,12370
--- 12373,12388
uses/entry
/row
row
+entryfunctionpg_get_owner_object/function(parameterrelation_oid/parameter)/entry
+entrytypetext/type/entry
+entryget name of the relation that owns the specified object, such as a sequence/entry
+ /row
+ row
+entryfunctionpg_get_owner_column/function(parameterrelation_oid/parameter)/entry
+entrytypetext/type/entry
+entryget column name associated with the specified object in its owning relation/entry
+ /row
+ row
entryfunctionpg_get_triggerdef/function(parametertrigger_oid/parameter)/entry
entrytypetext/type/entry
entryget commandCREATE [ CONSTRAINT ] TRIGGER/ command for trigger/entry
***
*** 12478,12483
--- 12496,12513
/para
para
+functionpg_get_owner_object/function returns the name of the relation
+that owns the specified relation object, or NULL if the object isn't owned
+by a relation. The input parameter can be passed as an OID or possibly a
+double-quoted identifier. This can be treated in some respects as the
+inverse of functionpg_get_serial_sequence/function, where the association
+can be modified or removed with commandALTER SEQUENCE OWNED BY/.
+functionpg_get_owner_column/function returns the name of the column
+associated with an owned object, such as the name of a sequence's
+original typeserial/ column.
+ /para
+
+ para
functionpg_get_userbyid/function extracts a role's name given
its OID.
/para
Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 1 Aug 2009 19:59:41 - 1.306
--- src/backend/utils/adt/ruleutils.c 2 Sep 2009 23:11:19 -
***
*** 1446,1451
--- 1446,1601
/*
+ * pg_get_owner_object
+ * Returns the name of the object that owns the specified object
+ * by looking up an auto dependency relationship.
+ * Useful for finding a sequence's parent table.
+ * See pg_get_owner_column for the originating serial column.
+ */
+ Datum
+ pg_get_owner_object(PG_FUNCTION_ARGS)
+ {
+ Oid relId = PG_GETARG_OID(0);
+ Oid ownerId = InvalidOid;
+ RelationdepRel;
+ ScanKeyData key[3];
+ SysScanDesc depScan;
+ HeapTuple tup;
+
+ /* Find the requested object in the dependency table... */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber,
