Re: renaming+recreating table w/default sequence causes dependency seq issue

Todd Kover <kovert(at)omniscient(dot)com> writes:
> I saw issues around renaming tables and not renaming sequences in the TODO
> list but did not see anything about this. Apologies if I missed it.

> This is with a 9.1.4 server (enterprisedb download on mac os/x lion) and also
> seen on 9.1.3 built from netbsd pkgsrc.

> It appears that something is amiss if you try to drop a table that has been
> renamed that used to have a default mapping to a sequence:

> Given this:
> ---<snip>---
> drop table IF EXISTS foo;
> drop table IF EXISTS foo_v26;

> create table foo (id serial not null, bar integer );
> alter table foo alter column id drop default;

> alter table foo rename to foo_v26;

> create table foo (id integer not null, bar integer );

> alter table foo alter id SET DEFAULT nextval('foo_id_seq');

> drop table foo_v26;
> ---<snip>---

> everthing works as expected until the final drop, which says:

> jazzhands=> drop table foo_v26;
> ERROR: cannot drop table foo_v26 because other objects depend on it
> DETAIL: default for table foo column id depends on sequence foo_id_seq
> HINT: Use DROP ... CASCADE to drop the dependent objects too.

I don't see any bug there. The ALTER DROP DEFAULT command does not
remove the sequence's dependence on foo.id; nor for that matter does
ALTER SET DEFAULT create an auto-drop dependency on the new table.
See ALTER SEQUENCE OWNED BY if you want to dissociate a serial column's
sequence from the column, or reattach it to another column.

Formally speaking, a "serial column" is shorthand for creating an
integer (or bigint) column, creating a sequence, associating them
as though by ALTER SEQUENCE OWNED BY, and setting the column's default
to nextval('sequence-name').

regards, tom lane