Re: [GENERAL] Use of Serial Datatype and Sequen

[Dave Page]

> -----Original Message-----
> From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] 
> Sent: 06 November 2001 07:59
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> 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.