Обсуждение: Is this a bug? (changing sequences in default value)
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?
Thanks!
Fernando.
On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
<fschapachnik@mecon.gov.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
merlin
En un mensaje anterior, Merlin Moncure escribió:
> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
> <fschapachnik@mecon.gov.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 ]
Thanks again!
Fernando.
On Fri, May 9, 2008 at 8:55 AM, Fernando Schapachnik
<fernando@mecon.gov.ar> wrote:
> En un mensaje anterior, Merlin Moncure escribió:
>> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
>> <fschapachnik@mecon.gov.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
En un mensaje anterior, Merlin Moncure escribió: [...] > >> > 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. Thanks for your help, but cascade doesn't make a difference. Fernando.
On Tue, May 13, 2008 at 8:50 AM, Fernando Schapachnik <fernando@mecon.gov.ar> wrote: > > > 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. > > Thanks for your help, but cascade doesn't make a difference. What do you mean? PostgreSQL 8.1 has 'drop sequence cascade': http://www.postgresql.org/docs/8.1/interactive/sql-dropsequence.html If this isn't working, can you paste the text of the error message? merlin
En un mensaje anterior, Merlin Moncure escribió: > On Tue, May 13, 2008 at 8:50 AM, Fernando Schapachnik > <fernando@mecon.gov.ar> wrote: > > > > 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. > > > > Thanks for your help, but cascade doesn't make a difference. > > What do you mean? PostgreSQL 8.1 has 'drop sequence cascade': > http://www.postgresql.org/docs/8.1/interactive/sql-dropsequence.html > > If this isn't working, can you paste the text of the error message? Sorry I wasn't clear. I mean to say that the error message is the same with or without cascade: sso=# drop SEQUENCE table1_id_seq cascade; ERROR: cannot drop sequence table1_id_seq because table ambitos column id requires it HINT: You may drop table table1 column id instead. Thanks. Fernando.