Thread: BUG #7802: Cannot drop table because of dependant sequence, but there is link to sequence

The following bug has been logged on the website:

Bug reference:      7802
Logged by:          Christiaan Willemsen
Email address:      cwillemsen@technocon.com
PostgreSQL version: 9.1.7
Operating system:   CentOS 6.2
Description:        =


I have a table that currently looks like this:

CREATE TABLE old_table
(
  id bigint DEFAULT 0
)

Thats's it.. =


So, now when I drop the table is sais:

ERROR:  cannot drop table old_table because other objects depend on it
DETAIL:  default for table old_table column id depends on sequence
old_table_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

But clearly, that is not the case... This was however the case. This table
was a partitioned table. I made a copy of the table to de-partition it, and
reused the old sequence of the new table, then removed the sequence from the
old table id. So for some reason Postgresql still thinks that the DEFAULT of
this id is still connected to the sequence. I already tried changing the
DEFAULT, truncate the table, drop all other columns, remove constraints and
indexes, vacuum full, basically all I could think of. Still, it wont work.

I don't want to drop the sequence in question, since it is in active use in
the new table.

I did exactly the same thing for another table that I de-partitioned, but
there I had not problems.

So how can I fix this?

cwillemsen@technocon.com writes:
> I have a table that currently looks like this:

> CREATE TABLE old_table
> (
>   id bigint DEFAULT 0
> )

> Thats's it..

> So, now when I drop the table is sais:

> ERROR:  cannot drop table old_table because other objects depend on it
> DETAIL:  default for table old_table column id depends on sequence
> old_table_id_seq
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.

> But clearly, that is not the case... This was however the case. This table
> was a partitioned table. I made a copy of the table to de-partition it, and
> reused the old sequence of the new table, then removed the sequence from the
> old table id. So for some reason Postgresql still thinks that the DEFAULT of
> this id is still connected to the sequence. I already tried changing the
> DEFAULT, truncate the table, drop all other columns, remove constraints and
> indexes, vacuum full, basically all I could think of. Still, it wont work.

Can you reconstruct exactly what you did there?  If it was all supported
ALTER commands, then it's definitely a bug that you ended up with a
bogus dependency, but without a clearer understanding of the process
I don't think we can find the bug.

As for getting out of the problem, what you need to do is manually
remove the appropriate item in the pg_depend catalog.  To do that,
first find out the OID of the pg_attrdef entry for table old_table
column id, then find out the OID of the sequence, then look for
a pg_depend entry with objid = first OID and refobjid = second OID.
Then, as superuser, manually DELETE that row.  (To be totally correct,
this recipe would need to also check classid and refclassid, but
the odds of collisions on both OIDs are so low I'm omitting that.
If you find more than one pg_depend entry that seems to fit the
bill, then you need to tread more carefully.)

The first part of that would go like this:
    select oid from pg_attrdef where adrelid = 'old_table'::regclass;
(you should get only one hit, if there's only one column left with
a default; otherwise look at adnum as well).  The second part is
    select 'old_table_id_seq'::regclass::oid;

If you'd like to understand what it is you're doing here, take a
look at the descriptions of these catalogs in
http://www.postgresql.org/docs/9.1/static/catalogs.html

If this all sounds too scary, a dump-and-restore would get rid of
the problem too.

            regards, tom lane