Re: Off-by-one oddity in minval for decreasing sequences

On Fri, Jan 6, 2017 at 2:15 PM, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> When testing the patch at https://commitfest.postgresql.org/12/768/
> ("sequence data type" by Peter E.), I notice that there's a preexisting
> oddity in the fact that sequences created with a negative increment
> in current releases initialize the minval to -(2^63)+1 instead of -2^63,
> the actual lowest value for a bigint.
>
> postgres=# CREATE SEQUENCE s INCREMENT BY -1;
> CREATE SEQUENCE
>
> postgres=# SELECT seqmin,seqmin+pow(2::numeric,63)
> FROM pg_sequence where seqrelid='s'::regclass;
> seqmin | ?column?
> ----------------------+--------------------
> -9223372036854775807 | 1.0000000000000000
>
> But it's still possible to set it to -2^63 manually either by
> altering the sequence or by specifying it explicitly at CREATE time
> with CREATE SEQUENCE s MINVALUE -9223372036854775808
> so it's inconsistent with the potential argument that we couldn't
> store this value for some reason.
>
> postgres=# ALTER SEQUENCE s minvalue -9223372036854775808;
> ALTER SEQUENCE
> postgres=# select seqmin,seqmin+pow(2::numeric,63)
> from pg_sequence where seqrelid='s'::regclass;
> seqmin | ?column?
> ----------------------+--------------------
> -9223372036854775808 | 0.0000000000000000
>
>
> The defaults comes from these definitions, in include/pg_config_manual.h
>
> /*
> * Set the upper and lower bounds of sequence values.
> */
> #define SEQ_MAXVALUE PG_INT64_MAX
> #define SEQ_MINVALUE (-SEQ_MAXVALUE)
>
> with no comment as to why SEQ_MINVALUE is not PG_INT64_MIN.
>
> When using other types than bigint, Peter's patch fixes the inconsistency
> but also makes it worse by ISTM applying the rule that the lowest value
> is forbidden for int2 and int4 in addition to int8.
>
> I'd like to suggest that we don't do that starting with HEAD, by
> setting seqmin to the real minimum of the supported range, because
> wasting that particular value seems silly and a hazard if
> someone wants to use a sequence to store any integer
> as opposed to just calling nextval().

This seems like a sensible argument to me, but maybe somebody's got a
contrary viewpoint?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company