Re: [GENERAL] Use of Serial Datatype and Sequen

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 06 November 2001 07:59
> To: pgsql-general(at)postgresql(dot)org
> Cc: pgadmin-hackers(at)postgresql(dot)org
> Subject: Re: [pgadmin-hackers] [GENERAL] Use of Serial
> Datatype and Sequence Issue
>
>
> At 00:28 02/11/01 +0000, you wrote:
> >I am new to postgres and am in the process of creating a new
> database
> >of several 100 tables. Most of the tables have a serial
> datatype. When
> >the table is created psql says it is creating an implicit sequence.
> >Yet when I drop the table and even when I drop the database the
> >sequence is still there. When I try to re-create the table it fails
> >beacuse the sequence already exists. This same behaviour exists for
> >indexes. How can I get rid of any sequences or indexes that
> have been
> >created. I would prefer not to explicitly drop each one. Ideally I
> >would like to drop the table and have all related indexes, sequences
> >dropped.
>
> pgAdmin2 knows how to drop indexes when dropping a table:
> http://pgadmin.postgresql.org
> It might not be interesting to drop a sequence as it can be used by
> multiple tables.
>
> Dave: do you think pgAdmin2 should drop a sequence when
> dropping a table?

No I don't, I think you're correct. I'm surprised by the comment that
indexes are left orphaned though - pgAdmin drops them first (well the dev
code does) but that's to maintain the revision log correctly - I thought
PostgreSQL had been dropping them as well since about v6.5(?):

helpdesk=# create table widget(id serial);

NOTICE: CREATE TABLE will create implicit sequence 'widget_id_seq' for
SERIAL column 'widget.id'

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'widget_id_key' for
table 'widget'

CREATE

helpdesk=# \d widget

Table "widget"

Attribute | Type | Modifier

-----------+---------+---------------------------------------------------

id | integer | not null default nextval('"widget_id_seq"'::text)

Index: widget_id_key

helpdesk=# \di widget
List of relations
Name | Type | Owner
---------------+-------+----------
widget_id_key | index | postgres
(1 row)

helpdesk=# drop table widget;
DROP
helpdesk=# \d widget
Did not find any relation named "widget".
helpdesk=# \di widget
No matching relations found.

That was on 7.1.2. There are no entries left in pg_index either...

Regards, Dave.