Thread: How to drop sequence?

How to drop sequence?

From

"Igor Kryltsov"

Date:

28 February 2004, 19:47:36

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

Re: How to drop sequence?

From

Ron St-Pierre

Date:

01 March 2004, 14:52:48

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

Re: How to drop sequence?

From

Ron St-Pierre

Date:

01 March 2004, 22:46:37

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
>>
>>
>>
>
>
>
>

Re: How to drop sequence?

From

Tom Lane

Date:

01 March 2004, 22:59:20

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

Re: How to drop sequence?

From

"scott.marlowe"

Date:

02 March 2004, 14:59:16

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.