Thread: bug# 2073 - Clarification

Bug- raised:
=========
BUG #2073: Can't drop sequence when created via SERIAL column

Answered:
========

Right.  We have this TODO item:
* %Disallow changing default expression of a SERIAL column?

which would prevent you from changing the default expression for a
SERIAL column.  So the answer is, don't do that, and in the future, we
might prevent it.

Clarification required:
=================

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 know
the expected output.


Thanks

Dhanaraj

Dhanaraj M <Dhanaraj.M@Sun.COM> writes:
> Pl. look at the following code, which is taken from alter_table.sql 
> (regression test)

The regression test is designed to check for the current behavior.
If we were to change the behavior, then of course we'd need to change
the regression test as well.

Personally I'm starting to like the idea of attaching the sequence's
dependency to the column default definition (ie, to the attrdef entry
not directly to the column).  That would mean that ALTER COLUMN DROP
DEFAULT would be allowed and would cause the sequence to go away.
I'm not sure about whether this would have any downsides, though.
        regards, tom lane