Thread: SERIAL type feature request
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. 2. Upon INSERTing to a serial column, explicitly given 0 value or 'default' keyword or omitted field (implicit default) should be interchangeable. 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. This is the way Informix handles its serial type, although it doesn't seem to have a visible sequence bound to the serial column. Is it feasible in the 8.2 timeframe? Thanks in advance, Zoltán Böszörményi
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. > 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. > 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 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 ;) > Is it feasible in the 8.2 timeframe? I hope not ;)
Zoltan Boszormenyi wrote: > Hi! > > I would like to add an entry to PostgreSQL 8.2 TODO: > - Extend SERIAL to a full-featured auto-incrementer type. > > I can't see this item on the TODO list at all. Where exactly did you find it? cheers andrew
Zoltan, > > I would like to add an entry to PostgreSQL 8.2 TODO: > > - Extend SERIAL to a full-featured auto-incrementer type. I believe that our SERIAL/SEQUENCE stuff is already in compliance with the SQL standard for sequences (in SQL03). Why would we change it? -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > I believe that our SERIAL/SEQUENCE stuff is already in compliance > with the SQL standard for sequences (in SQL03). Why would we change > it? Because your belief is wrong, but Zoltan's proposal is not getting is closer. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 12/3/2005 4:23 PM, Zoltan Boszormenyi wrote: > 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. Syntactic sugar with zero real value. A setval() after create table does exactly the same. Unless you extend your proposal to unambiguosly specify any or all of the serials properties (min, max, start, cache, cycle), this has to be rejected as incomplete. > > 2. Upon INSERTing to a serial column, explicitly given 0 value or > 'default' keyword > or omitted field (implicit default) should be interchangeable. Why exactly would we treat an explicit zero different from any other explicit value? What you are asking for is to substitute an explicit, literal value presented by the user with something different. Sorry, but if Informix does THAT, then Informix is no better than MySQL. > > 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. How about negative increment values, cycling sequences and max/minval? > > This is the way Informix handles its serial type, although it doesn't seem > to have a visible sequence bound to the serial column. Have you considered asking Informix to do the reverse changes? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Andrew Dunstan írta: > > > Zoltan Boszormenyi wrote: > >> Hi! >> >> I would like to add an entry to PostgreSQL 8.2 TODO: >> - Extend SERIAL to a full-featured auto-incrementer type. >> >> > I can't see this item on the TODO list at all. Where exactly did you > find it? That's why I wanted it ADDed... ;-) Best regards
Peter Eisentraut írta: >Josh Berkus wrote: > > >>I believe that our SERIAL/SEQUENCE stuff is already in compliance >>with the SQL standard for sequences (in SQL03). Why would we change >>it? >> >> > >Because your belief is wrong, but Zoltan's proposal is not getting is >closer. > > > OK, what does the standard say on SERIAL for specifying the start value? And about this: <last serial value was e.g. 307> insert into mytable (serial_id, ...) values (500, ...); delete from mytable where serial_id = 500; In Informix, this is a way to start the next serial value at 501. Best regards, Zoltán
Zoltan Boszormenyi wrote: > Peter Eisentraut ?rta: > > >Josh Berkus wrote: > > > > > >>I believe that our SERIAL/SEQUENCE stuff is already in compliance > >>with the SQL standard for sequences (in SQL03). Why would we change > >>it? > >> > >> > > > >Because your belief is wrong, but Zoltan's proposal is not getting is > >closer. > > > > > > > OK, what does the standard say on SERIAL for specifying the start value? > And about this: > > <last serial value was e.g. 307> > insert into mytable (serial_id, ...) values (500, ...); > delete from mytable where serial_id = 500; > > In Informix, this is a way to start the next serial value at 501. This seems much stranger than a simple setval(), which get got from Oracle. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Zoltan Boszormenyi wrote: > Andrew Dunstan írta: > >> >> >> Zoltan Boszormenyi wrote: >> >>> Hi! >>> >>> I would like to add an entry to PostgreSQL 8.2 TODO: >>> - Extend SERIAL to a full-featured auto-incrementer type. >>> >>> >> I can't see this item on the TODO list at all. Where exactly did you >> find it? > > > > That's why I wanted it ADDed... ;-) > > I misread. I apologise. However, I think it's up to you to demonstrate why the Informix way of doing things is better than what we have, on a matter on which (AFAICS) the standard is silent. What you propose would be backwards incompatible, which we try to avoid. cheers andrew
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 ;)
>
>
Am Sonntag, den 04.12.2005, 08:52 +0100 schrieb Zoltan Boszormenyi:
> OK, I admit I haven't read the SQL standards on this matter.
>
> Tino Wildenhain írta:
>
...
> 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.
There is no serial type ;) serial is only a macro which boils down
to int4/int8 and a default value of nextval('some_sequence')
This is a little bit kludgy, but I dont know how much you would
gain from a true type.
> >
> >
> >>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:
This does not mean we should magically translate values to something
other. We arent MySQL. We are ACID.
> >>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
Well, you can use setval() for this. Why would you want to do this
inbound? The whole point of sequences is not to set a value
explicitely. Who is the first who set it? And why and when
should it fail?
After all, if you want a year in the number, use a year.
e.g. prepend your serials with to_char(now(),'YYYY')
...
> >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.
Maybe you can translate these Informixisms to the way postgres
works. It is always some work to migrate from one db to another.
Its quite popular with MySQL->postgres, but I think you should
get by with Informix as well. There arent just so many howtows
on that matter by now.
If you have special issues you need to solve, just ask on the
list for ideas. But I really doubt there is really a point
to modify postgres to the way a slow and sucky database works .-)
++Tino
Jan Wieck írta: > On 12/3/2005 4:23 PM, Zoltan Boszormenyi wrote: > >> 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. > > > Syntactic sugar with zero real value. A setval() after create table > does exactly the same. Unless you extend your proposal to unambiguosly > specify any or all of the serials properties (min, max, start, cache, > cycle), this has to be rejected as incomplete. I found this in the SQL2003 draft: " 4.14.7 Identity columns ... An identity column has a start value, an increment, a maximum value, a minimum value, and a cycle option. ... " The exact properties of a sequence. It would be a good idea to be able to provide all these the same way PostgreSQL provides CREATE SEQUENCE. >> 2. Upon INSERTing to a serial column, explicitly given 0 value or >> 'default' keyword >> or omitted field (implicit default) should be interchangeable. > > > > Why exactly would we treat an explicit zero different from any other > explicit value? What you are asking for is to substitute an explicit, > literal value presented by the user with something different. Sorry, > but if Informix does THAT, then Informix is no better than MySQL. Thinking about it more, 0 is a special value that a sequence created with defaults (just like the ones created for SERIAL fields) will not produce. If PostgreSQL provides a way to specify the sequence parameters for a SERIAL, there may be other values too, that a sequence created with given parameters will not produce. At the extreme, they may be handled the same way. E.g. CREATE SEQUENCE seq1 INCREMENT 2 MINVALUE 2 MAXVALUE 100; won't produce 0, 1, any odd number between 3 and 99, and numbers 101 ... 2^64 -1. >> 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. > > > > How about negative increment values, cycling sequences and max/minval? For descending sequences, a lower value should update the 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. > > > > Have you considered asking Informix to do the reverse changes? Hm. Good idea. I'll try. But I guess they won't backport it to 9.21. :-) Best regards, Zoltán Böszörményi
Zoltan Boszormenyi wrote: > > > I found this in the SQL2003 draft: > > " > 4.14.7 Identity columns > > ... An identity column has a start value, an increment, a maximum > value, a minimum value, > and a cycle option. ... And that section says nothing at all about using 0 as a magic value. All it says is: "Let IC be the identity column of BT. When a row R is presented for insertion into BT, if R does not contain a column corresponding to IC, then the value V for IC in the row inserted into BT is obtained by applying the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”, to SG." Which is pretty much what we do. We can't implement everybody's way of doing serial cols, because they are simply not all compatible. Next someone will be asking us to do it MySQL's way (gods forbid). cheers andrew
On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: > I found this in the SQL2003 draft: > > " > 4.14.7 Identity columns > > ... An identity column has a start value, an increment, a maximum value, > a minimum value, > and a cycle option. ... > " > > The exact properties of a sequence. It would be a good idea to be able > to provide > all these the same way PostgreSQL provides CREATE SEQUENCE. I think nobody would object to implementing support for the SQL2003 syntax. Most of that would be providing all the values that will get forwarded into the internal sequence generation during CREATE TABLE. The other thing needed is an extension to the default value mechanism that overrides any given value to implement GENERATE ALLWAYS. Not too hard either. > Thinking about it more, 0 is a special value that a sequence created > with defaults > (just like the ones created for SERIAL fields) will not produce. If > PostgreSQL Zero is no more special than any other value and the standard you pointed to does not talk about it either. If we implement IDENTITY as per standard, you will either omit the value, specify DEFAULT or declare the column GENERATE ALLWAYS. If we had to pick any magic value I would vote for skipping 666 in all sequence generators and use that. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Dec 5, 2005, at 9:50 , Jan Wieck wrote: > If we had to pick any magic value I would vote for skipping 666 in > all sequence generators and use that. What about 13? Michael Glaesemann grzm myrealbox com
> I think nobody would object to implementing support for the SQL2003 > syntax. Most of that would be providing all the values that will get > forwarded into the internal sequence generation during CREATE TABLE. Someone also pointed out on IRC the other day that Oracle and DB2 list 'identity' as the column type of identity columns in the information_schema, whereas we just put 'integer'. Maybe we could change that to match in the future... Chris
Jan Wieck írta: > On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: > >> I found this in the SQL2003 draft: >> >> " >> 4.14.7 Identity columns >> >> ... An identity column has a start value, an increment, a maximum >> value, a minimum value, >> and a cycle option. ... >> " >> >> The exact properties of a sequence. It would be a good idea to be >> able to provide >> all these the same way PostgreSQL provides CREATE SEQUENCE. > > > I think nobody would object to implementing support for the SQL2003 > syntax. Most of that would be providing all the values that will get > forwarded into the internal sequence generation during CREATE TABLE. > > The other thing needed is an extension to the default value mechanism > that overrides any given value to implement GENERATE ALLWAYS. Not too > hard either. Where can I find this syntax? (PDF file name, page#) Thanks. I think I modify my feature request for the standard behaviour. Thanks and best regards, Zoltán Böszörményi
On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote: > Jan Wieck írta: > >> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: >> >>> I found this in the SQL2003 draft: >>> >>> " >>> 4.14.7 Identity columns >>> >>> ... An identity column has a start value, an increment, a maximum >>> value, a minimum value, >>> and a cycle option. ... >>> " >>> >>> The exact properties of a sequence. It would be a good idea to be >>> able to provide >>> all these the same way PostgreSQL provides CREATE SEQUENCE. >> >> >> I think nobody would object to implementing support for the SQL2003 >> syntax. Most of that would be providing all the values that will get >> forwarded into the internal sequence generation during CREATE TABLE. >> >> The other thing needed is an extension to the default value mechanism >> that overrides any given value to implement GENERATE ALLWAYS. Not too >> hard either. > > > Where can I find this syntax? (PDF file name, page#) Thanks. > I think I modify my feature request for the standard behaviour. It's all in the Foundation paper inside this zip: http://www.wiscorp.com/sql/sql_2003_standard.zip Jan > > Thanks and best regards, > Zoltán Böszörményi > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck írta: > On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote: > >> Jan Wieck írta: >> >>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: >>> >>>> I found this in the SQL2003 draft: >>>> >>>> " >>>> 4.14.7 Identity columns >>>> >>>> ... An identity column has a start value, an increment, a maximum >>>> value, a minimum value, >>>> and a cycle option. ... >>>> " >>>> >>>> The exact properties of a sequence. It would be a good idea to be >>>> able to provide >>>> all these the same way PostgreSQL provides CREATE SEQUENCE. >>> >>> >>> >>> I think nobody would object to implementing support for the SQL2003 >>> syntax. Most of that would be providing all the values that will get >>> forwarded into the internal sequence generation during CREATE TABLE. >>> >>> The other thing needed is an extension to the default value >>> mechanism that overrides any given value to implement GENERATE >>> ALLWAYS. Not too hard either. >> >> >> >> Where can I find this syntax? (PDF file name, page#) Thanks. >> I think I modify my feature request for the standard behaviour. > > > It's all in the Foundation paper inside this zip: > > http://www.wiscorp.com/sql/sql_2003_standard.zip Thanks, I found it. It's "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY, isn't it? If I interpret it correctly, "GENERATED ALWAYS AS IDENTITY" means that no matter what I give in "INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...)", the sequence next value will be inserted into the database. I am all for it, it's much stronger than just watching for the 0 value and would fit my needs. The other behaviour is "GENERATED BY DEFAULT AS IDENTITY", which is what PostgreSQL currently provides. Best regards, Zoltán Böszörményi
Hi, Zoltan Boszormenyi írta: > Jan Wieck írta: > >> On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote: >> >>> Jan Wieck írta: >>> >>>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: >>>> >>>>> I found this in the SQL2003 draft: >>>>> >>>>> " >>>>> 4.14.7 Identity columns >>>>> >>>>> ... An identity column has a start value, an increment, a maximum >>>>> value, a minimum value, >>>>> and a cycle option. ... >>>>> " >>>>> >>>>> The exact properties of a sequence. It would be a good idea to be >>>>> able to provide >>>>> all these the same way PostgreSQL provides CREATE SEQUENCE. >>>> >>>> >>>> >>>> >>>> I think nobody would object to implementing support for the SQL2003 >>>> syntax. Most of that would be providing all the values that will >>>> get forwarded into the internal sequence generation during CREATE >>>> TABLE. >>>> >>>> The other thing needed is an extension to the default value >>>> mechanism that overrides any given value to implement GENERATE >>>> ALLWAYS. Not too hard either. >>> >>> >>> >>> >>> Where can I find this syntax? (PDF file name, page#) Thanks. >>> I think I modify my feature request for the standard behaviour. >> >> >> >> It's all in the Foundation paper inside this zip: >> >> http://www.wiscorp.com/sql/sql_2003_standard.zip > > > > Thanks, I found it. It's "GENERATED { ALWAYS | BY DEFAULT } AS > IDENTITY, isn't it? > If I interpret it correctly, "GENERATED ALWAYS AS IDENTITY" means that > no matter > what I give in "INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...)", > the sequence > next value will be inserted into the database. I am all for it, it's > much stronger than just watching > for the 0 value and would fit my needs. > > The other behaviour is "GENERATED BY DEFAULT AS IDENTITY", > which is what PostgreSQL currently provides. > > Best regards, > Zoltán Böszörményi To reiterate it, I would like the following added to PostgreSQL 8.2 TODO (I may have got the optional parametes wrong...): - Extend SERIAL type declaration and functionality with the SQL2003 compliant sequence generation options: SERIAL [ GENERATED { ALWAYS | BY DEFAULT } [ AS IDENTITY ( [ START WITHstartvalue ] [ INCREMENT BY incrementvalue ] [ MAXVALUE maxvalue ] [ MINVALUE minvalue ] [ CYCLE | NO CYCLE ] ) ] ]