Обсуждение: How to drop sequence?
Hi,
I have table:
# \d category;
category_id | integer | not null default
nextval('public.category_category_id_seq'::text)
category_name | character varying(100) | not null
Indexes: category_pkey primary key btree (category_id)
My goal is to remove sequence from category_id column and remove it after
from DB.
First I tried:
DROP SEQUENCE category_category_id_seq - fails saying that table category
column category_id uses it
Than I tried:
ALTER TABLE category ALTER COLUMN category_id DROP DEFAULT;
Now category_id column is shown as integer not null only but :
DROP SEQUENCE category_category_id_seq - fails saying that table category
column category_id uses it again
Any suggestions?
Thank you,
Igor
Igor Kryltsov wrote:
>Hi,
>
>I have table:
>
>
># \d category;
> category_id | integer | not null default
>nextval('public.category_category_id_seq'::text)
> category_name | character varying(100) | not null
>Indexes: category_pkey primary key btree (category_id)
>
>My goal is to remove sequence from category_id column and remove it after
>from DB.
>
>First I tried:
>
>DROP SEQUENCE category_category_id_seq - fails saying that table category
>column category_id uses it
>
>Than I tried:
>
>ALTER TABLE category ALTER COLUMN category_id DROP DEFAULT;
>
>Now category_id column is shown as integer not null only but :
>
>DROP SEQUENCE category_category_id_seq - fails saying that table category
>column category_id uses it again
>
>
>Any suggestions?
>
>Thank you,
>
>
>Igor
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
Try
DROP SEQUENCE category_category_id_seq CASCADE;
Ron
You're right I am getting the same results. I created the same table with:
create table category (
category_id serial not null primary key,
category_name character varying(100) not null
);
alter table category alter column category_id drop default;
ALTER TABLE
drop sequence public.category_category_id_seq;
ERROR: cannot drop sequence category_category_id_seq because table
category column category_id requires it
HINT: You may drop table category column category_id instead.
and it won't let me drop the sequence, even if I drop the default for
the column first. Does anyone know if this is the way this is supposed
to work, and if so, how to remove the dependency on it from the column
category_id?
Ron
ps you should (also) reply to the list.
Igor Kryltsov wrote:
>Hi Ron,
>
>Thank you for your mail.
>
>I tried - result is confusing (there is no default on a column but sequence still can not be dropped)
>I can not believe that column has to be dropped in order to remove sequence which is nothing to do with column after
droppingdefault on a column.
>
>
>
>amity_wa=# DROP SEQUENCE category_category_id_seq CASCADE;
>ERROR: Cannot drop sequence category_category_id_seq because table category column category_id requires it
> You may drop table category column category_id instead
>
>amity_wa=# \d category
> Table "public.category"
> Column | Type | Modifiers
>---------------+------------------------+-------------------------------------------------------------------
> category_id | integer | not null default nextval('public.category_category_id_seq'::text)
> category_name | character varying(100) | not null
>Indexes: category_pkey primary key btree (category_id)
>
>amity_wa=# alter table category alter column category_id drop default;
>ALTER TABLE
>
>amity_wa=# DROP SEQUENCE category_category_id_seq CASCADE;
>ERROR: Cannot drop sequence category_category_id_seq because table category column category_id requires it
> You may drop table category column category_id instead
>
>amity_wa=# \d category
> Table "public.category"
> Column | Type | Modifiers
>---------------+------------------------+-----------
> category_id | integer | not null
> category_name | character varying(100) | not null
>Indexes: category_pkey primary key btree (category_id)
>
>
>
>"Ron St-Pierre" <rstpierre@syscor.com> wrote in message news:<40435C84.4010401@syscor.com>...
>
>
>>Igor Kryltsov wrote:
>>
>>
>>
>>>Hi,
>>>
>>>I have table:
>>>
>>>
>>># \d category;
>>> category_id | integer | not null default
>>>nextval('public.category_category_id_seq'::text)
>>>category_name | character varying(100) | not null
>>>Indexes: category_pkey primary key btree (category_id)
>>>
>>>My goal is to remove sequence from category_id column and remove it after
>>>
>>>
>>>from DB.
>>
>>
>>>First I tried:
>>>
>>>DROP SEQUENCE category_category_id_seq - fails saying that table category
>>>column category_id uses it
>>>
>>>Than I tried:
>>>
>>>ALTER TABLE category ALTER COLUMN category_id DROP DEFAULT;
>>>
>>>Now category_id column is shown as integer not null only but :
>>>
>>>DROP SEQUENCE category_category_id_seq - fails saying that table category
>>>column category_id uses it again
>>>
>>>
>>>Any suggestions?
>>>
>>>Thank you,
>>>
>>>
>>>Igor
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/docs/faqs/FAQ.html
>>>
>>>
>>>
>>>
>>Try
>>
>>DROP SEQUENCE category_category_id_seq CASCADE;
>>
>>Ron
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>>
>>
>>
>
>
>
>
Ron St-Pierre <rstpierre@syscor.com> writes:
> Does anyone know if this is the way this is supposed
> to work,
Yes it is. The fact that there is a sequence involved in a SERIAL
column is really an implementation detail that you're not supposed to
muck with. Thus the dependency is on the column itself; drop the column
if you want to make the sequence go away.
(There should probably be some defense against letting you mess with the
column default expression, too, but we don't have one at the moment.)
If this isn't how you'd like things to work, don't use the SERIAL
shorthand. Create the sequence yourself.
regards, tom lane
On Mon, 1 Mar 2004, Ron St-Pierre wrote: > You're right I am getting the same results. I created the same table with: > create table category ( > category_id serial not null primary key, > category_name character varying(100) not null > ); > > alter table category alter column category_id drop default; > ALTER TABLE > > drop sequence public.category_category_id_seq; > ERROR: cannot drop sequence category_category_id_seq because table > category column category_id requires it > HINT: You may drop table category column category_id instead. > > and it won't let me drop the sequence, even if I drop the default for > the column first. Does anyone know if this is the way this is supposed > to work, and if so, how to remove the dependency on it from the column > category_id? OK, here's a story... Once upon a time, whenever you created a table with a SERIAL column, it was implemented with a sequence that had no dependency tracking. This meant that when you dropped the table, the sequence was still there. Given the simplistic method used to assign the name of a sequence created by the SERIAL macro, this meant that future attempts to create said table again would fail. Witness, the wonder of postgresql 7.2: postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test' CREATE postgres=# drop table test; DROP postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test' ERROR: Relation 'test_id_seq' already exists But, with 7.3 the dependency tracking system started keeping track of the sequences created by the SERIAL macro, thus making it possible to have these things disappear when uneeded. This is with 7.4: postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id" CREATE TABLE postgres=# drop table test; DROP TABLE postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id" CREATE TABLE postgres=# Note there's now no error with an undropped sequence. But, as with all progress, it came with a price. In the past, many users had used the serial macro and gotten used to the behaviour it exhibited, including myself, I must admit. The agreement was made that from then on, if you wanted sequences to be tracked by dependency, use serial, if you want to have them be standalone you'd have to create them yourself. Maybe there's a more complex way of handling dependencies that might fix this minor issue, like automatically tracking everytime a sequence is assigned to a clause in another table, but that might get caught in circular references and go kaboom if I wrote it. :-) Now, if you want to uncouple them, you're playing in the database catalogs, which is as close to an unsupported feature as anything in postgresql can be. Do a \dS in a psql session to see all the public catalog. I'm not even sure where to start myself, and I wouldn't recommend doing anything to the catalogs on a production server.