Hi,
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().
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
