How to drop sequence?

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:

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.