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 (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers