Re: Patch for bug #2073 (Can't drop sequence when created

Pl. look at the following code, which is taken from alter_table.sql
(regression test)

=========================================================================

mydb=# create table anothertab (atcol1 serial8, atcol2 boolean,
constraint anothertab_chk check (atcol1 <= 3));
NOTICE:  CREATE TABLE will create implicit sequence
"anothertab_atcol1_seq" for serial column "anothertab.atcol1"
CREATE TABLE

mydb=# alter table anothertab alter column atcol1 drop default;
ALTER TABLE

mydb=# \d

                  List of relations
 Schema |         Name          |   Type   |  Owner
--------+-----------------------+----------+----------
 public | anothertab            | table    | dm199272
 public | anothertab_atcol1_seq | sequence | dm199272

(2 rows)

mydb=# drop sequence anothertab_atcol1_seq;
ERROR:  cannot drop sequence anothertab_atcol1_seq because table
anothertab column atcol1 requires it
HINT:  You may drop table anothertab column atcol1 instead.

=========================================================================

Please tell me whether statement-2 is valid or not (as you say that the
default sequence should not be changed).
Or the default seq. can be dropped and cant be  changed. I like to send
you the appropriate patch and waiting for your reply.

Thanks
Dhanaraj

Tom Lane wrote:

>Dhanaraj M - Sun Microsystems <dhanaraj.m@mail-apac.sun.com> writes:
>
>
>>I fixed the above bug. I attach the patch here. Please review and
>>acknowledge me.
>>
>>
>
>
>
>>Bug details
>>========
>>BUG #2073: Can't drop sequence when created via SERIAL column
>>
>>
>
>That isn't a bug, and this "fix" is not appropriate.  See eg Bruce's
>response to that bug report:
>http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php
>
>I speculated a bit ago (can't find it in the archives at the moment)
>that we should abandon the hidden dependency altogether, and go back to
>having SERIAL just create the sequence and the default expression.
>Another idea that might be worth exploring is to link the sequence to
>the default expression rather than to the table column itself.  But
>randomly dropping the dependency is not the answer.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>