Обсуждение: renaming+recreating table w/default sequence causes dependency seq issue

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.

however...

jazzhands=> \d foo;
                         Table "public.foo"
 Column |  Type   |                    Modifiers
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('foo_id_seq'::regclass)

jazzhands=> \d foo_v26;
    Table "public.foo_v26"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null

Interestingly, I can drop table foo without any complaints.

It seems like the dependency did not move (it also seems like its
backwards but that's probably all me).

Sadly, if I move setting the default to after I drop the old table, the
sequence goes away, so I am still digging into a work around.

thanks,
-Todd

Excerpts from Todd Kover's message of mar ago 07 20:10:25 -0400 2012:

> Given this:
> ---<snip>---
> drop table IF EXISTS foo;
> drop table IF EXISTS foo_v26;
>=20
> create table foo (id serial not null, bar integer );
> alter table foo alter column id drop default;
>=20
> alter table foo rename to foo_v26;
>=20
> create table foo (id integer not null, bar integer );
>=20
> alter table foo alter id SET DEFAULT nextval('foo_id_seq');
>=20
> drop table foo_v26;
> ---<snip>---
>=20
> everthing works as expected until the final drop, which says:
>=20
> jazzhands=3D> 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 think you need an ALTER SEQUENCE .. OWNED BY to fix up dependencies
for the sequence.

--=20
=C3=81lvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Todd Kover <kovert@omniscient.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

> 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').

I stand corrected.  It would be nice if \ds or \ds+ or something showed
this relationship so it was evident.  That's more of a feature request
than a bug fix tho.

apologies for the misdirection.

-Todd