Re: [HACKERS] Sequences.... - Mailing list pgsql-hackers

It has been put on hold, I'll begin/continue working on it when we are done with 
the beta :)

-Ryan 

> Can I ask where we are with this.
> 
> > Hello hackers...
> > 
> > I've spent the last couple of evening tracing through the drop 
table/sequence 
> > code trying to figure out the best to drop the sequence when the table is 
> > dropped.
> > 
> > Here is what I am proposing to do.  I just wanted to throw out my idea and 
get 
> > some feedback since I am just beginning to understand how the backend works.
> > 
> > Take the following example:
> > CREATE TABLE foo (i SERIAL, t text);
> > 
> > This creates table foo, index foo_i_key, and the sequence foo_i_seq.
> > 
> > The sequence ocuppies three of the system tables: pg_class, pg_attribute, 
and 
> > pg_attrdef.  When the table gets dropped, the table foo and foo_i_key are 
> > removed.  The default portion of the sequence is also removed from the 
> > pg_attrdef system table, because the attrelid matches the table's oid.  
> > 
> > I believe this is incorrect ... I think the attrelid should match the 
seqences 
> > oid instead of the table's oid to prevent the following error:
> > 
> > ryan=> CREATE TABLE foo (i SERIAL, t text);
> > NOTICE:  CREATE TABLE will create implicit sequence foo_i_seq for SERIAL 
column 
> > foo.i
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index foo_i_key for table 
foo
> > CREATE
> > 
> > ryan=> \d
> > 
> > Database    = ryan
> >  +------------------+----------------------------------+----------+
> >  |  Owner           |             Relation             |   Type   |
> >  +------------------+----------------------------------+----------+
> >  | rbrad            | foo                              | table    |
> >  | rbrad            | foo_i_key                        | index    |
> >  | rbrad            | foo_i_seq                        | sequence |
> >  +------------------+----------------------------------+----------+
> > 
> > ryan=> \d foo;
> > 
> > Table    = foo
> > 
+----------------------------------+----------------------------------+-------+
> > |              Field               |              Type                | 
Length|
> > 
+----------------------------------+----------------------------------+-------+
> > | i                                | int4 not null default nextval('f |     
4 |
> > | t                                | text                             |   
var |
> > 
+----------------------------------+----------------------------------+-------+
> > Index:    foo_i_key
> > 
> > ryan=> drop sequence foo_i_seq;
> > DROP
> > 
> > ryan=> \d
> > 
> > Database    = ryan
> >  +------------------+----------------------------------+----------+
> >  |  Owner           |             Relation             |   Type   |
> >  +------------------+----------------------------------+----------+
> >  | rbrad            | foo                              | table    |
> >  | rbrad            | foo_i_key                        | index    |
> >  +------------------+----------------------------------+----------+
> > ryan=> \d foo;
> > 
> > Table    = foo
> > 
+----------------------------------+----------------------------------+-------+
> > |              Field               |              Type                | 
Length|
> > 
+----------------------------------+----------------------------------+-------+
> > | i                                | int4 not null default nextval('f |     
4 |
> > | t                                | text                             |   
var |
> > 
+----------------------------------+----------------------------------+-------+
> > Index:    foo_i_key
> > 
> > ryan=> insert into foo (t) values ('blah');
> > ERROR:  foo_i_seq.nextval: sequence does not exist
> > 
> > ryan=>
> > 
> > This looks pretty easy to fix.
> > 
> > Back to my origional point .. I think we need another system table to map 
the 
> > sequence oid to the table's oid.  I've noticed this done with the 
inheritance, 
> > indexes, etc ... but I don't see a pg_sequence table.
> > 
> > I would be glad to try and finish this in the next couple of evenings if 
this 
> > looks like the correct approach to the problem, otherwise could someone 
point me 
> > in the right direction :)
> > 
> > Thanks,
> > -Ryan
> > 
> > 
> 
> 
> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>