Re: [HACKERS] Sequences....

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
>