Re: invisible dependencies on a table?

Tim Uckun <timuckun(at)gmail(dot)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'::regclass and 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'::regclass and 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