Re: BUG #7802: Cannot drop table because of dependant sequence, but there is link to sequence - Mailing list pgsql-bugs

<p>Hi Tom,<p> <p>Thanks for the answer. I will try to fix the problem via the catalog. As for how I did this.. I hope I
cangive you a full walkthough:<p> <p>- We've had a paritioned table with 1 primary key, and two foreign keys (one of
themhas in index). There were about three partions (all made via a trigger on the mother table<p>- We wanted to
de-paritionthis table, so I did:<p style="margin-left: 40px;">SELECT * INTO new_table FROM old_table<p>- then made the
newprimary key,<p>- made it use the old sequence of old_table,<p>- created a new index of the foreign keys<p>- renamed
old_tableto old_table_tmp<p>- renamed new_table to old_table<p>- removed the paritions of old_table_tmp (previously
old_table)<p>-tried removing old_table_tmp<p> <p>And then I tied all the things I described earlier. I hope this helps.
AsI said, I did this in a similar way for another table without problems, but I'm not sure if I did everything in the
exactsame order.<p> <p>Kind regards,<p> <p>Christiaan<p> <blockquote style="border-left: 2px solid #325FBA;
padding-left:5px;margin-left:5px;">-----Original message-----<br /><strong>From:</strong> Tom Lane
<tgl@sss.pgh.pa.us><br/><strong>Sent:</strong> Thu 10-01-2013 17:10<br /><strong>Subject:</strong> Re: [BUGS] BUG
#7802:Cannot drop table because of dependant sequence, but there is link to sequence<br /><strong>To:</strong>
ChristiaanWillemsen <cwillemsen@technocon.com>; <br /><strong>CC:</strong> pgsql-bugs@postgresql.org; <br
/>cwillemsen@technocon.comwrites:<br />> I have a table that currently looks like this:<br /><br />> CREATE TABLE
old_table<br/>> (<br />>   id bigint DEFAULT 0<br />> )<br /><br />> Thats's it.. <br /><br />> So, now
whenI drop the table is sais:<br /><br />> ERROR:  cannot drop table old_table because other objects depend on it<br
/>>DETAIL:  default for table old_table column id depends on sequence<br />> old_table_id_seq<br />> HINT:
 UseDROP ... CASCADE to drop the dependent objects too.<br /><br />> But clearly, that is not the case... This was
howeverthe case. This table<br />> was a partitioned table. I made a copy of the table to de-partition it, and<br
/>>reused the old sequence of the new table, then removed the sequence from the<br />> old table id. So for some
reasonPostgresql still thinks that the DEFAULT of<br />> this id is still connected to the sequence. I already tried
changingthe<br />> DEFAULT, truncate the table, drop all other columns, remove constraints and<br />> indexes,
vacuumfull, basically all I could think of. Still, it wont work.<br /><br />Can you reconstruct exactly what you did
there? If it was all supported<br />ALTER commands, then it's definitely a bug that you ended up with a<br />bogus
dependency,but without a clearer understanding of the process<br />I don't think we can find the bug.<br /><br />As for
gettingout of the problem, what you need to do is manually<br />remove the appropriate item in the pg_depend catalog.
 Todo that,<br />first find out the OID of the pg_attrdef entry for table old_table<br />column id, then find out the
OIDof the sequence, then look for<br />a pg_depend entry with objid = first OID and refobjid = second OID.<br />Then,
assuperuser, manually DELETE that row.  (To be totally correct,<br />this recipe would need to also check classid and
refclassid,but<br />the odds of collisions on both OIDs are so low I'm omitting that.<br />If you find more than one
pg_dependentry that seems to fit the<br />bill, then you need to tread more carefully.)<br /><br />The first part of
thatwould go like this:<br /> select oid from pg_attrdef where adrelid = 'old_table'::regclass;<br />(you should get
onlyone hit, if there's only one column left with<br />a default; otherwise look at adnum as well).  The second part
is<br/> select 'old_table_id_seq'::regclass::oid;<br /><br />If you'd like to understand what it is you're doing here,
takea<br />look at the descriptions of these catalogs in<br
/>http://www.postgresql.org/docs/9.1/static/catalogs.html<br/><br />If this all sounds too scary, a dump-and-restore
wouldget rid of<br />the problem too.<br /><br /> regards, tom lane<br /></blockquote>