1

In my PostgreSQL 9.6 database, INSERT on a certain table is not possible anymore: The primary key column is an integer and the last_value of the sequence in question is 2,147,484,010 - which is too big for an integer. As a consequence, I get 'integer out of range'.

So far, so good.

The question is: How come? Why is last_value so huge?

The actual count of rows is just 112,698. The table rows are not deleted normally.

And there seems to be no general, reproducible problem. I've tried with the following: I copied that database, deleted all rows from the table in question, reset the sequence, and created entries with an (almost) 'real life query'. I could create more than 6 million rows with any problems.

I know that sequences are not designed to be gapless (e.g. on the above-mentioned test I got a last_value of 8,946,095 for 6,404,000 rows), however having a value 2 billion bigger than the row count seem pretty strange to me. Obviously, something went wrong here.

How could I debug this further?

Edit: data of the sequence is as follows:

+------------+-------------+--------------+-----------------------+-----------+-------------+---------+-----------+-----------+
| last_value | start_value | increment_by | max_value             | min_value | cache_value | log_cnt | is_cycled | is_called |
| 2147484010 | 1           | 1            | 9,22337203685478E+018 | 1         | 1           | 32      | False     | True      |
+------------+-------------+--------------+-----------------------+-----------+-------------+---------+-----------+-----------+
  • How are your sequence values distributed in your table? – Lennart Jul 16 '18 at 16:06
  • What's the cache size for the sequence? – mustaccio Jul 16 '18 at 17:38
  • @mustaccio cache_value is 1. See my edit. – cis Jul 17 '18 at 5:50
  • 3
    What does select max(id) from that table give you? Maybe something/someone ran select nextval('...') from generate_series(1,1e10) or something similar and burned those numbers. Under "normal" circumstances it takes ages to reach the "end" of a sequence. I don't think you will find out how that happened after the fact. Just reset the sequence to max(id) and move on – a_horse_with_no_name Jul 17 '18 at 7:23
  • 1
    pseudo_encrypt(1) returns 56146585. It gets stored in the table. Later if you do pseudo_encrypt(561465857) it returns 1 which is the original value. – Daniel Vérité Jul 18 '18 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.