Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sequence id NOT incremented as expected

Sequence id NOT incremented as expected

From
SINGHAL Harsh
Date:
FACTS :

 1. Sequences are based on bigint arithmetic, so the range cannot exceed
    the range of an eight-byte integer (-9223372036854775808 to
    9223372036854775807). On some older platforms, there may be no
    compiler support for eight-byte integers, in which case sequences
    use regular integer arithmetic (range -2147483648 to +2147483647).
 2. The CYCLE option allows the sequence to wrap around when the
    maxvalue or minvalue has been reached by an ascending or descending
    sequence respectively. If the limit is reached, the next number
    generated will be the minvalue or maxvalue, respectively.

Reality :

  * version
    PostgreSQL 8.4.12 on x86_64-pc-linux-gnu, compiled by GCC
    gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

  * Colonne|Type|Valeur
    sequence_name|name|xxx_id_seq
    last_value|bigint|1
    start_value|bigint|1
    increment_by|bigint|1
    max_value|bigint|9223372036854775807
    min_value|bigint|1
    cache_value|bigint|1
    log_cnt|bigint|32
    is_cycled|boolean|t
    is_called|boolean|t

  * In an increasing sequence whose max value is set to
    9223372036854775807 and cycle option is activated, as shown above,
    from 2147483647 to 9223372036854775807 it is displaying error
    "ERREUR:  entier en dehors des limites" and after that it resets it
    to 1 which is normal for any sequence whose cycle option is activated.
  * Can you please tell what to do during the interval (2147483647 to
    9223372036854775807) or How to restart the sequence at 2147483647 ?

--
--
Harsh Raj SINGHAL
Cy-Play

Re: Sequence id NOT incremented as expected

From
Tom Lane
Date:
SINGHAL Harsh <harsh.singhal@cy-play.com> writes:
>   * In an increasing sequence whose max value is set to
>     9223372036854775807 and cycle option is activated, as shown above,
>     from 2147483647 to 9223372036854775807 it is displaying error
>     "ERREUR:  entier en dehors des limites" and after that it resets it
>     to 1 which is normal for any sequence whose cycle option is activated.

You didn't show us any context here, but this sounds like you're trying
to store the sequence's result into a integer (not bigint) column.
If so, that's not a bug, it's just pilot error.

            regards, tom lane

Re: Sequence id NOT incremented as expected

From
SINGHAL Harsh
Date:
Thanks a lot, Tom.

--
Harsh Raj SINGHAL
Cy-Play

On 20/07/12 16:08, Tom Lane wrote:
> SINGHAL Harsh <harsh.singhal@cy-play.com> writes:
>>    * In an increasing sequence whose max value is set to
>>      9223372036854775807 and cycle option is activated, as shown above,
>>      from 2147483647 to 9223372036854775807 it is displaying error
>>      "ERREUR:  entier en dehors des limites" and after that it resets it
>>      to 1 which is normal for any sequence whose cycle option is activated.
> You didn't show us any context here, but this sounds like you're trying
> to store the sequence's result into a integer (not bigint) column.
> If so, that's not a bug, it's just pilot error.
>
>             regards, tom lane
>