Re: SERIAL type feature request

OK, I admit I haven't read the SQL standards on this matter.

Tino Wildenhain írta:

>Am Samstag, den 03.12.2005, 22:23 +0100 schrieb Zoltan Boszormenyi:
>  
>
>>Hi!
>>
>>I would like to add an entry to PostgreSQL 8.2 TODO:
>>- Extend SERIAL to a full-featured auto-incrementer type.
>>
>>To achieve this, the following three requirements should be fulfilled:
>>
>>1. The statement parser should be able to handle this:
>>
>>create table x (
>>    id serial(N),
>>    ...
>>);
>>
>>and behind the scenes this would translate into the "create sequence ... 
>>start N"
>>before creating the table.
>>    
>>
>
>why isnt N max_id? Or increment?
>Sounds inconsistent. -1 on this.
>  
>

A SERIAL type has the assumption that its value starts at a low value 
(1) and
is increasing. Or is there a type modifier keyword that makes it work 
backwards?
A start value would also work here, decreasing from there.

>  
>
>>2. Upon INSERTing to a serial column, explicitly given 0 value or 
>>'default' keyword
>>or omitted field (implicit default) should be interchangeable.
>>    
>>
>
>default and omit are these. 0 would be an error. -1 on this too.
>  
>
Why? A sequence in PostgreSQL won't give you 0 even in wraparound mode.
I just checked it:

db=> create sequence proba_seq maxvalue 5 cycle;
CREATE SEQUENCE
db=> select nextval('proba_seq');nextval
---------      1
(1 sor)

db=> select nextval('proba_seq');nextval
---------      2
(1 sor)

db=> select nextval('proba_seq');nextval
---------      3
(1 sor)

db=> select nextval('proba_seq');nextval
---------      4
(1 sor)

db=> select nextval('proba_seq');nextval
---------      5
(1 sor)

db=> select nextval('proba_seq');nextval
---------      1
(1 sor)


>>3. When a serial field value is given in an INSERT or UPDATE statement
>>and the value is larger the the current value of the sequence then the 
>>sequence
>>should be modified accordingly.
>>    
>>
>
>sideeffects, raceconditions. -1 on this.
>  
>
This event doesn't (shouldn't) occur often, e.g. you have an invoice 
table, invoice No.
contains the year, too. It's somewhat natural to handle it with the 
serial field, so
it gives out 200500001 ... values. At the beginning of the next year, 
you modify
the sequence to start at 200600001. What I mean is that there may be two 
paths
in the serial field handling, one where 'default' is used, it's 
hopefully isn't racy
as this is the way it works now. The other is when the value is 
explicitly given,
a little critical section may not hurt:

Lock sequence
Check the current value of section
If given value is higher Then Modify sequence
Unlock sequence

>>This is the way Informix handles its serial type, although it doesn't seem
>>to have a visible sequence bound to the serial column.
>>    
>>
>
>Sounds like this informix is seriously broken ;)
>  
>

Yes, and slow, too. :-( That's why I would like to port the company's 
software to PostgreSQL
but there way too many places where "Informixism" were used.

>>Is it feasible in the 8.2 timeframe?
>>    
>>
>
>I hope not ;)
>  
>