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

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?

Postgres version is PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)

Thanks,

David.

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

David Brain <dbrain@bandwidth.com> writes:
> 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.

The "ownership" link is still there, evidently, and should be switched
to the new table.  Read up on ALTER SEQUENCE OWNED BY.

            regards, tom lane

Hi,

On Thu, Sep 10, 2009 at 2:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>
> The "ownership" link is still there, evidently, and should be switched
> to the new table.  Read up on ALTER SEQUENCE OWNED BY.
>
>                        regards, tom lane
>

Thank you - that was the issue, once the ownership was switched to the
new tables I was able to drop the old ones.  That's certainly
something I've learned about Postgres today.

David