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

Hi Tom,

 
Thanks for the answer. I will try to fix the problem via the catalog. As for how I did this.. I hope I can give you a full walkthough:

 
- We've had a paritioned table with 1 primary key, and two foreign keys (one of them has in index). There were about three partions (all made via a trigger on the mother table

- We wanted to de-parition this table, so I did:

SELECT * INTO new_table FROM old_table

- then made the new primary key,

- made it use the old sequence of old_table,

- created a new index of the foreign keys

- renamed old_table to old_table_tmp

- renamed new_table to old_table

- removed the paritions of old_table_tmp (previously old_table)

- tried removing old_table_tmp

 
And then I tied all the things I described earlier. I hope this helps. As I said, I did this in a similar way for another table without problems, but I'm not sure if I did everything in the exact same order.

 
Kind regards,

 
Christiaan

 
-----Original message-----
From:Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent:Thu 10-01-2013 17:10
Subject:Re: [BUGS] BUG #7802: Cannot drop table because of dependant sequence, but there is link to sequence
To:Christiaan Willemsen <cwillemsen(at)technocon(dot)com>;
CC:pgsql-bugs(at)postgresql(dot)org;
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