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

cwillemsen(at)technocon(dot)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