Re: Unable to drop a table due to seemingly non-existent dependencies

David Brain wrote:
> Hi,
>
> I have a situation where trying to drop a table results in:
>
> #drop table cdrimporterror_old;
> NOTICE:  default for table cdrimporterror column cdrimporterrorid
> depends on sequence cdrimporterror_cdrimporterrorid_seq
> ERROR:  cannot drop table cdrimporterror_old because other objects depend on it
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
> Now as you can probably imply from the name if this table, this table
> has been renamed from cdrimporterror to cdrimporterorr_old and I have
> (or at least thought I had) removed it's dependency on the sequence by
> changing the tables column type (from bigserial to bingint) and
> removing the default value.  In fact this table no longer has any
> constraints or indexes either.
>
> Where would I look to find what was causing Postgres to still be
> seeing the sequence as a dependant?

There's a dependency-tracking table pg_depend. Assuming you don't want
to just drop the sequence and re-create one with the same name you'll
probably need to delete the relevant row from there*.

You can track the row via object oids. Example from a db here on a table
called "summary":
SELECT oid,relname FROM pg_class WHERE relname='summary';
   oid   | relname
---------+---------
 4747904 | summary
(1 row)

SELECT oid,relname FROM pg_class WHERE relname='summary_cid_seq';
   oid   |     relname
---------+-----------------
 4748275 | summary_cid_seq
(1 row)

SELECT * FROM pg_depend WHERE objid = 4748275;
 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid |
deptype
---------+---------+----------+------------+----------+-------------+---------
    1259 | 4748275 |        0 |       2615 |  4747647 |           0 | n
    1259 | 4748275 |        0 |       1259 |  4747904 |           1 | a
(2 rows)

Clearly it's the second row you want to delete.


* Can't think of a problem doing this, but it will void your warranty.

--
  Richard Huxton
  Archonet Ltd