Re: [HACKERS] Sequences....

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026