Re: [HACKERS] Sequences.... - Mailing list pgsql-hackers
From | Bruce Momjian |
---|---|
Subject | Re: [HACKERS] Sequences.... |
Date | |
Msg-id | 199905100042.UAA04217@candle.pha.pa.us Whole thread Raw |
In response to | Sequences.... (Ryan Bradetich <rbrad@hpb50023.boi.hp.com>) |
List | pgsql-hackers |
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
pgsql-hackers by date: