Re: Is this a bug? (changing sequences in default value)

On Fri, May 9, 2008 at 8:55 AM, Fernando Schapachnik
<fernando(at)mecon(dot)gov(dot)ar> wrote:
> En un mensaje anterior, Merlin Moncure escribió:
>> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
>> <fschapachnik(at)mecon(dot)gov(dot)ar> wrote:
>> > Pg 8.1.11, I try to change sequences as default value of a table, then
>> > remove old sequence:
>> >
>> > # \d table1
>> > Table "table1"
>> > Column | Type | Modifiers
>> > --------+---------+---------------------------------------------------------------
>> > id | integer | not null default nextval('table1_id_seq'::regclass)
>> > nombre | text | not null
>> > Indexes:
>> > "table1_pkey" PRIMARY KEY, btree (id)
>> >
>> > # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
>> > ALTER TABLE
>> >
>> > # \d table1
>> > Table "table1"
>> > Column | Type | Modifiers
>> > --------+---------+---------------------------------------------------------------
>> > id | integer | not null default nextval('newseq_id_seq'::regclass)
>> > nombre | text | not null
>> > Indexes:
>> > "table1_pkey" PRIMARY KEY, btree (id)
>> >
>> > # drop SEQUENCE table1_id_seq ;
>> > ERROR: cannot drop sequence table1_id_seq because table
>> > table1 column id requires it
>> > HINT: You may drop table table1 column id instead.
>> >
>> > Am I doing something wrong?
>>
>> yes and no when you created the table initially you probably made it
>> a 'serial' column which set up the ownership that prevents the drop
>> operation. that ownership did not go away when you altered the
>> default to the new serial.
>>
>> to fix this,
>> alter sequence sequence table1_id_seq owned by none; -- now you can drop
>
> Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
> of this syntax I tried gives me error as, apparently, it should:
>
> \h ALTER SEQUENCE
> Command: ALTER SEQUENCE
> Description: change the definition of a sequence generator
> Syntax:
> ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
> [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
> MAXVALUE ]
> [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

oop, you are using 8.1 :-). This was added in a later version. drop
sequence ... cascade should probably work. you can try it out in a
transaction to be sure.

merlin