CODEWITHSUNDEEP
postgresql
Denis BUCHER
Denis BUCHER

Reputation: 320

Where to find object dependencies in Postgresql ? (pg_depend)

In Postgresql 8.1.23, I want to know on which objects my table is depending.

I did :

SELECT * FROM pg_depend WHERE objid=
  (SELECT oid FROM pg_class WHERE relname = 'prix_articleclient');
 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+---------+----------+------------+----------+-------------+---------
    1259 | 5877366 |        0 |       2615 |    24605 |           0 | n

My question is : what is the object '24605'? I searched in dozens of tables, webpages, forums, including stackoverflow, but was not able to find any help.

Any help will therefore be greatly appreciated.

P.S. Note that I tried "\d+ your_table" and "Find dependent objects for a table or view" without success

P.P.S. I also tried that, but it seems to lead to nowhere :

# SELECT * FROM pg_class WHERE oid = 2615 ;
   relname    | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass |    relacl
 pg_namespace |           11 |   10275 |       10 |     0 |        2615 |             0 |        1 |         5 |             0 |             0 | t           | f           | r       |        3 |         0 |           0 |        0 |        0 |       0 | t          | f          | f           | f              | {=r/postgres}

# SELECT * FROM pg_type WHERE oid=10275;
   typname    | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput  | typoutput  | typreceive  |   typsend   | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault
 pg_namespace |           11 |       10 |     -1 | f        | c       | t            | ,        |     2615 |       0 | record_in | record_out | record_recv | record_send | -          | d        | x          | f          |           0 |        -1 |        0 |               |

P.P.P.S. As suggested by Erwin Brandstetter :

SELECT *, refclassid::regclass AS referenced_class FROM pg_depend
WHERE objid = 'prix_articleclient'::regclass;"

gives this result :

 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid | deptype | referenced_class
---------+---------+----------+------------+----------+-------------+---------+------------------
    1259 | 5877366 |        0 |       2615 |    24605 |           0 | n       | pg_namespace

And then :

SELECT * FROM pg_namespace WHERE oid = 24605;

...returns the schema.

Which means this dependency was the schema.

Upvotes: 0

Views: 9833

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656441

Quoting the manual for 8.1 (!):

refobjid oid any OID column The OID of the specific referenced object

You have to interpret that in combinatioin with:

refclassid oid pg_class.oid The OID of the system catalog the referenced object is in

A shortcut to find out about that is the cast to regclass:

SELECT *, refclassid::regclass AS referenced_class
FROM   pg_depend
WHERE  objid = 'prix_articleclient'::regclass

Underlying problem

The underlying problem described to your link is probably that the sequences are not owned by their respective columns. More details:

But why beat on a dead horse?
PostgreSQL 8.1 is just too old. EOL 2010. Upgrade to a current version.

Upvotes: 2

Related Questions