Обсуждение: Problem removing Sequence in Postgresql 8.0.3

Hi

I'm trying to remove a sequence from my production database (postgresql
8.0.3 running on RHEL4 PowerPC), but am getting an error that doesn't
make sense. When I run:

      DROP SEQUENCE tbl_id_seq1 RESTRICT;

I get the following error:

      ERROR:  cannot drop sequence tbl_id_seq1 because table tbl column
      id requires it
      HINT:  You may drop table tbl column id instead.


but I have updated the table to use the sequence tbl_id_seq.

      mydb=# \d tbl
      Table "public.tbl"
        Column  |         Type         |                    Modifiers
      ----------+----------------------+-------------------------------------------------
       id       | integer              | not null default
       nextval('tbl_id_seq'::text)
       code     | character(6)         | not null
       business | character varying    | not null
       h_indx   | character varying(2) | not null
      Indexes:
         "tbl_pkey" PRIMARY KEY, btree (id)
         "tbl_business_key" UNIQUE, btree (code)

And if I check the tables using this sequence, I get nothing:


      mydb=# SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a
      ON (p.relfilenode = a.adrelid) WHERE a.adsrc ~ 'tbl_id_seq1';
        relname | adsrc
       ---------+-------
       (0 rows)

Can anyone suggest how I might remove this sequence from my database (I
would like to avoid dropping and then recreating the table tbl)?


Thanks
Adam
--
  That Fat Guy
  thatfatguy@fastmail.fm

--
http://www.fastmail.fm - IMAP accessible web-mail

That Fat Guy wrote:
> Hi
>
> I'm trying to remove a sequence from my production database (postgresql
> 8.0.3 running on RHEL4 PowerPC), but am getting an error that doesn't
> make sense. When I run:

In 8.2 and later (or was it 8.1?) there is ALTER SEQUENCE ... OWNED BY
to solve this problem.  In 8.0 I think you'd have to resort to manually
playing with the system catalogs (pg_depend in particular) to do the
same that the command would do.

Note that this is dangerous and unsopported, and if you break your
database it'll be your fault only, but it does work.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.