Re: [GENERAL] Use of Serial Datatype and Sequen

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: "'Jean-Michel POURE'" <jm(dot)poure(at)freesurf(dot)fr>, pgsql-general(at)postgresql(dot)org
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: [GENERAL] Use of Serial Datatype and Sequen
Date: 2001-11-06 08:17:05
Message-ID: AA30E7BCCA5C1D4E88A231900F8325C00C42@dogbert.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers pgsql-general

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

Browse pgadmin-hackers by date

  From Date Subject
Next Message Tom Lane 2001-11-06 18:54:09 Re: Use of Serial Datatype and Sequence Issue
Previous Message Jean-Michel POURE 2001-11-06 07:58:40 Re: [GENERAL] Use of Serial Datatype and Sequence Issue

Browse pgsql-general by date

  From Date Subject
Next Message Jean-Michel POURE 2001-11-06 08:19:54 Re: Drop column and multiple db query
Previous Message Carlo F. Florendo 2001-11-06 08:12:13 pg_dump error