Re: How to drop sequence? - Mailing list pgsql-general

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.