The Integer at the End of the Universe: Integer... | Crunchy Data Blog
Jesse Soyland
Mar 3, 2023·7 min read
More by this author
Latest Articles<br>Postgres Serials Should be BIGINT (and How to Migrate)<br>Postgres 18 New Default for Data Checksums and How to Deal with Upgrades<br>PostGIS Performance: Simplification<br>Postgres Scan Types in EXPLAIN Plans<br>PostGIS Performance: Data Sampling
Production Postgres<br>The Integer at the End of the Universe: Integer Overflow in Postgres
Jesse Soyland
Mar 3, 2023·7 min read·More by this author
Integer overflow occurs when a computer program tries to store an integer but the value being stored exceeds the maximum value that can be represented by the data type being used to store it. We have helped a few Crunchy Data clients navigate this recently and wanted to write up some notes.In Postgres, there are three integer types:smallint - A 2-byte integer, -32768 to 32767integer- A 4-byte integer, -2147483648 to 2147483647bigint - An 8-byte integer, -9223372036854775808 to +9223372036854775807It is not uncommon to use a 4-byte integer as a primary key when defining a new table. This can cause problems if the value to be represented is more than 4-bytes can hold. If a sequence’s limit is reached you might see an error in your logs that looks like this:ERROR: nextval: reached maximum value of sequence "test_id_seq" (2147483647)<br>Don’t Panic! We have some helpful and intelligible PostgreSQL solutions.How do you know if you are close to overflowing an integer?<br>The following query will identify any auto-incrementing columns, which SEQUENCE object it owns, data types of the column and SEQUENCE object, and percent until the sequence value exceeds the sequence or column data type:SELECT<br>seqs.relname AS sequence,<br>format_type(s.seqtypid, NULL) sequence_datatype,<br>CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,<br>format_type(attrs.atttypid, atttypmod) AS column_datatype,<br>pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,<br>TO_CHAR((<br>CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)<br>WHEN format_type(s.seqtypid, NULL) = 'integer' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)<br>WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)<br>END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,<br>TO_CHAR((<br>CASE WHEN format_type(attrs.atttypid, NULL) = 'smallint' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)<br>WHEN format_type(attrs.atttypid, NULL) = 'integer' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)<br>WHEN format_type(attrs.atttypid, NULL) = 'bigint' THEN<br>(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)<br>END) * 100, 'fm9999999999999999999990D00%') AS column_percent<br>FROM<br>pg_depend d<br>JOIN pg_class AS seqs ON seqs.relkind = 'S'<br>AND seqs.oid = d.objid<br>JOIN pg_class AS tbls ON tbls.relkind = 'r'<br>AND tbls.oid = d.refobjid<br>JOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjid<br>AND attrs.attnum = d.refobjsubid<br>JOIN pg_sequence s ON s.seqrelid = seqs.oid<br>WHERE<br>d.deptype = 'a'<br>AND d.classid = 1259;<br>To show this query in action, let me set up a test table with an integer primary key, where the sequence has been artificially advanced to 2 Billion:postgres=# create table test(id serial primary key, value integer);<br>CREATE TABLE<br>postgres=# select setval('test_id_seq', 2000000000);<br>setval<br>2000000000<br>(1 row)
postgres=# \d test<br>Table "public.test"<br>Column | Type | Collation | Nullable | Default<br>--------+---------+-----------+----------+----------------------------------<br>id | integer | | not null | nextval('test_id_seq'::regclass)<br>value | integer | | |<br>Indexes:<br>"test_pkey" PRIMARY KEY, btree (id)<br>Now when running the query above to find the integer overflow percent, I can see that that the data types for both the column and the sequence are both integer, and since the sequence’s next value is 2 Billion, it is 93% through the acceptable range:sequence | sequence_datatype | owned_by | column_datatype | last_sequence_value | sequence_percent | column_percent<br>-------------+-------------------+----------+-----------------+---------------------+------------------+----------------<br>test_id_seq | integer | test.id | integer | 2000000001 | 93.13% | 93.13%<br>(1 row)<br>Changing to negative number sequencing<br>Since the integer types in Postgres include negative numbers, a simple way to deal with integer overflow is to flip to sequencing with negative numbers. This can be done by giving the sequence a new start value of -1 and converting to a descending sequence by giving it a negative INCREMENT value:alter sequence test_id_seq no minvalue start with -1 increment -1 restart;<br>If the purpose of the generated key is purely to create uniqueness, negative values are perfectly acceptable, but in some application frameworks or other use cases negative numbers may be...