The following bug has been logged online:

Bug reference: 2336
Logged by: Chris Mair
Email address: chris (AT) 1006 (DOT) org
PostgreSQL version: 8.1.3
Operating system: MacOS X 10.3.9
Description: will not drop implicitly created sequence
Details:

Ok, so this is probably not a bug at all - just slightly unexpected
behaviour.

This piece of SQL:
---------------------------------------------------
create table test2 (id serial, txt varchar);
-- (will create implicit sequence "test2_id_seq")
alter table test2 alter id set default 123;
drop sequence test2_id_seq;
---------------------------------------------------

would say:
---------------------------------------------------
ERROR: 2BP01: cannot drop sequence test2_id_seq because table test2 column
id requires it
HINT: You may drop table test2 column id instead.
LOCATION: recursiveDeletion, dependency.c:472
---------------------------------------------------

as if test2.id would still use the sequence.

Contrast this with the same thing done with an explicitly declared
sequence:
---------------------------------------------------
create sequence test1_id_seq;
create table test1 (id int not null default nextval('test1_id_seq'), txt
varchar);
alter table test1 alter id set default 123;
drop sequence test1_id_seq;
---------------------------------------------------

which will just work fine.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

TODO has:

* %Disallow changing default expression of a SERIAL column

so we know it is a bug.

---------------------------------------------------------------------------

Chris Mair wrote:
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match