SERIAL type feature request

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 #

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.

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, Pennsylvania 19073

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

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

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

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 WITH startvalue ] [ INCREMENT BY
incrementvalue ]
[ MAXVALUE maxvalue ] [
MINVALUE minvalue ] [ CYCLE | NO CYCLE ] ) ] ]