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

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.