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