Is this a bug? (changing sequences in default value)
This is a discussion on Is this a bug? (changing sequences in default value) within the Pgsql General forums, part of the PostgreSQL category; --> Pg 8.1.11, I try to change sequences as default value of a table, then remove old sequence: # \d ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-10-2008, 03:04 PM
| ||||
| ||||
 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general      |
|
05-10-2008, 03:04 PM
| |||
| |||
| Re: Is this a bug? (changing sequences in default value) 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-10-2008, 03:04 PM
| |||
| |||
| Re: Is this a bug? (changing sequences in default value) 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-10-2008, 03:04 PM
| |||
| |||
| Re: Is this a bug? (changing sequences in default value) 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-13-2008, 07:14 PM
| |||
| |||
| Re: Is this a bug? (changing sequences in default value) 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: Is this a bug? (changing sequences in default value) 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/i...psequence.html If this isn't working, can you paste the text of the error message? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
|
05-13-2008, 07:15 PM
| ||||
| ||||
| Re: Is this a bug? (changing sequences in default value) 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/i...psequence.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
Linear Mode
