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(dot)m(at)mail-apac(dot)sun(dot)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
>
>