From: | Ryan Bradetich <rbrad(at)hpb50023(dot)boi(dot)hp(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Sequences.... |
Date: | 1999-03-15 10:12:20 |
Message-ID: | 199903151012.DAA03251@hpb50023.boi.hp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Broytmann | 1999-03-15 12:23:19 | Re: [HACKERS] VACUUM ANALYZE problem on linux |
Previous Message | Hiroshi Inoue | 1999-03-15 10:00:10 | RE: [HACKERS] libpq and SPI |