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

Should I try to finish this before 6.5? Or wait for the next release?

-Ryan

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