Thread: BUG #8582: field serial getted incorrect value from automaticaly created its sequence

The following bug has been logged on the website:

Bug reference:      8582
Logged by:          Evgeniy Skomorokhov
Email address:      evgeniy.skomorokhov@gmail.com
PostgreSQL version: 9.2.4
Operating system:   ubuntu precise 12.04
Description:

Automaticaly created sequence takes values ​​from the range [1;
9223372036854775807] but max value of field which will be filled from
sequence - max of type integer (2147483647).


In PostgreSQL we can set field type "serial" during creting table like:
CREATE TABLE test1(
id serial
);


Then we get table and sequence structure like
/*
NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"


-- Created structures test1 and test1_id_seq:


CREATE TABLE test1
(
  id serial NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test1
  OWNER TO gis;




CREATE SEQUENCE test1_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE test1_id_seq
  OWNER TO gis;
*/


And when we insert values to table we can get exception "ERROR: integer out
of range"

evgeniy.skomorokhov@gmail.com writes:
> Automaticaly created sequence takes values ​​from the range [1;
> 9223372036854775807] but max value of field which will be filled from
> sequence - max of type integer (2147483647).

If you want a bigint column, use "bigserial".  Not everybody wants
to take that space penalty, though.

            regards, tom lane

On 11/7/2013 10:41 AM, Tom Lane wrote:
> evgeniy.skomorokhov@gmail.com  writes:
>> >Automaticaly created sequence takes values ​​from the range [1;
>> >9223372036854775807] but max value of field which will be filled from
>> >sequence - max of type integer (2147483647).
> If you want a bigint column, use "bigserial".  Not everybody wants
> to take that space penalty, though.


I think he's suggesting that a sequence generated from a regular serial
probably shouldn't have that 63 bit max_value



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

John R Pierce wrote
> On 11/7/2013 10:41 AM, Tom Lane wrote:
>>=20

> evgeniy.skomorokhov@

>   writes:
>>> >Automaticaly created sequence takes values =C3=A2=E2=82=AC=E2=80=B9=C3=
=A2=E2=82=AC=E2=80=B9from the range [1;
>>> >9223372036854775807] but max value of field which will be filled from
>>> >sequence - max of type integer (2147483647).
>> If you want a bigint column, use "bigserial".  Not everybody wants
>> to take that space penalty, though.
>=20
>=20
> I think he's suggesting that a sequence generated from a regular serial=
=20
> probably shouldn't have that 63 bit max_value

The main advantage of the current behavior is if you hit the limit for an
integer you can alter the column type to bigint without having to mess
around with the associated sequence.  If the sequence was upper-bound
limited that same conditions that caused the out-of-range error will just
generate a different kind of error (not sure which one if the sequence is
exhausted).

Its definitely not a "bug" whether you believe the behavior should be
changed or not.  A sequence is de-coupled from any particular type.  Maybe
if there were short, normal, and big sequences with matching performance
improvements it would matter but having only a single sequence
implementation makes the current behavior/defaults reasonable for normal
usage.

I am curious why this even came up.  This seems to be the kind of thing mos=
t
people would be oblivious to.

David J.








--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8=
582-field-serial-getted-incorrect-value-from-automaticaly-created-its-seque=
nce-tp5777360p5777396.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On 11/7/2013 11:16 AM, David Johnston wrote:
> If the sequence was upper-bound
> limited that same conditions that caused the out-of-range error will just
> generate a different kind of error (not sure which one if the sequence is
> exhausted).

indeed.    and being curious myself, the error is...

ERROR:  nextval: reached maximum value of sequence "t_id_seq" (20)



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast