Re: invisible dependencies on a table? - Mailing list pgsql-general

Tim Uckun <timuckun@gmail.com> writes:
> BTW is there a way to get a list of dependencies for a object? I was some
> scripts when I was googling but none of them seem to work with later
> versions of postgres.

Don't know why that would be; the pg_depend data structure hasn't really
changed since it was invented (in 7.3, if memory serves).  If anything,
it's gotten easier to work with, as a result of invention of helper
functions such as pg_describe_object().

regression=# create table foo (f1 serial);
CREATE TABLE
regression=# -- things foo depends on:
regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from pg_depend where
classid='pg_class'::regclassand objid = 'foo'::regclass; 
 pg_describe_object | deptype
--------------------+---------
 schema public      | n
(1 row)
regression=# -- things that depend on foo:
regression=# select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where
refclassid='pg_class'::regclassand refobjid = 'foo'::regclass; 
       pg_describe_object        | deptype
---------------------------------+---------
 type foo                        | i
 sequence foo_f1_seq             | a
 default for table foo column f1 | a
(3 rows)

It's that automatic dependency of the sequence on the table (or, if you
drill down a little further by looking at refobjsubid, you'll find out
it's really depending specifically on the f1 column) that represents
the owned-by relationship.

This is a nice way to look at the contents of pg_depend:

regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid)as refobj, deptype from pg_depend order by objid desc limit 10; 
               obj               |       refobj        | deptype
---------------------------------+---------------------+---------
 default for table foo column f1 | sequence foo_f1_seq | n
 default for table foo column f1 | table foo column f1 | a
 type foo                        | table foo           | i
 type foo[]                      | type foo            | i
 table foo                       | schema public       | n
 type foo_f1_seq                 | sequence foo_f1_seq | i
 sequence foo_f1_seq             | schema public       | n
 sequence foo_f1_seq             | table foo column f1 | a
 function wait_for_stats()       | language plpgsql    | n
 function wait_for_stats()       | schema public       | n
(10 rows)

See
http://www.postgresql.org/docs/9.3/static/catalog-pg-depend.html
for some documentation about what the deptype means.

            regards, tom lane