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@sss.pgh.pa.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@technocon.com>;
CC:pgsql-bugs@postgresql.org;
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
Hi Tom,
Back again ;) I just checked pg_depend, and according to it there is no relation between in de table and the sequence... There is however a relation between the new table and the old sequence (as expected). So this is sill a mistery.
By the way, I can't to a dump restore, it would take hours and this is a production system. Otherwise I might try dropping the sequence, dropping the old_table, and the just recreate the sequence. But that's something I would not like to do if it can be avoided.
Kind regards,
Christiaan
-----Original message-----
From:Tom Lane <tgl@sss.pgh.pa.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@technocon.com>;
CC:pgsql-bugs@postgresql.org;
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
Import Notes
Resolved by subject fallback
=?utf-8?Q?Christiaan_Willemsen?= <cwillemsen@technocon.com> writes:
Back again ;) I just checked pg_depend, and according to it there is no relation between in de table and the sequence... There is however a relation between the new table and the old sequence (as expected). So this is sill a mistery.
Hm, I might have gotten the direction of the link backwards --- if you
swap objid and refobjid, do you find an entry linking the sequence to
the old table's default?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
=?utf-8?Q?Christiaan_Willemsen?= <cwillemsen@technocon.com> writes:
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:
This isn't nearly detailed enough to give me any hope of reproducing the
problem. Any chance you can dredge up the exact command sequence?
Perhaps from your ~/.psql_history, or postmaster log files?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi Tom,
To bad, I don't have the exact logging... I do however need to de-partition two more tables, probably this week, so I will do some more extensive statement logging and I I get stuck again, I'll post it.
Kind regards,
Christiaan Willemsen
-----Original message-----
From:Tom Lane <tgl@sss.pgh.pa.us>
Sent:Thu 10-01-2013 18:44
Subject:Re: [BUGS] BUG #7802: Cannot drop table because of dependant sequence, but there is link to sequence
To:Christiaan Willemsen <cwillemsen@technocon.com>;
CC:pgsql-bugs@postgresql.org;
=?utf-8?Q?Christiaan_Willemsen?= <cwillemsen@technocon.com> writes:
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:
This isn't nearly detailed enough to give me any hope of reproducing the
problem. Any chance you can dredge up the exact command sequence?
Perhaps from your ~/.psql_history, or postmaster log files?
regards, tom lane