Thread: 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
> 

Ryan Bradetich wrote:
> > 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.

Sounds good.

As long as a sequence can point to more than 
one table/column combination.

Or, I guess, you can have the relationship 1-1 for
the SERIAL type, but this should not prevent using
sequences across more than one table if you don't use SERIAL.
I often use a sequence for 3 or more tables in a system
so that I can use 'generic' functions on the tables
and produce reports without conficting primary keys..

:) Clark

Ryan Bradetich wrote:
> >
> > 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:

pg_attrdef->attrelid is used to store DEFAULT definition
for particular attribute -> DEFAULT part of SERIAL definition 
will not work after this...

> >
> > 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.

Sequences and tables are independent things - that's why
there was no pg_sequence table. Currently each sequence
has row in pg_class, i.e. sequences are special tables.
But I agreed that we need in new table to reflect
SERIAL <--> sequence dependencies.

Vadim