Sequences.... - Mailing list pgsql-hackers

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