Thread: Sequences....
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
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,
Pennsylvania19026