CODEWITHSUNDEEP
linuxpostgresql
rRr
rRr

Reputation: 408

Can hard restart of machine hosting PostgreSQL change PostgreSQL sequence?

I have this case on my work table.

Customer has restarted (at least 2 times) the machine on which PostgreSQL was running. After that, the nextval of a sequence on one column has changed.

The last value before restart was 582. After restart it should return 583, but instead it returned 615.

I have checked all possible logs, from linux system logs through PostgreSQL logs until our app logs, no sight for anything calling nextval on this row.

So I tried the crazy idea, and translated the numbers into bits..

583 in bits: 0010 0100 0111
615 in bits: 0010 0110 0111

There is only one bit difference. So, is it possible that one bit got messed with from the hard restart???

There really are not much option how would be this nextval called 33 times in that time. The time difference between call that returned 582 and the one that returned 615 is only like hour or so and in that time was PC twice restarted. Yes, it is long time in programming, but there is no sight of calling nextval during that time.

Edit #1:

I have checked cache_value, it is 1 (as it probably should be). Also increment_by is 1 too. So there shouldn't be any allocated values. The code that is calling nextval is connected to hardware switch (Cashbox sensor), when activated, it finds out Cashbox state and insert it into table whose id is this sequence. There are some pretty heavy selects done before new row is inserted, so if it was called, there would be some footprints in either our app logs or PostgreSQL logs.

The reason why do I care is, that this sequence is used for IDs on cashbox changes log, so gap in the IDs doesn't look good, even that we can prove there has been nothing done between those 2 IDs.

Upvotes: 3

Views: 115

Answers (1)

StvnBrkdll
StvnBrkdll

Reputation: 4044

Please refer to this Postgres documentation for details on how sequences are generated in Postgres.

To quote the documentation:

So, any numbers allocated but not used within a session will be lost when that session ends, resulting in "holes" in the sequence.

It is likely that what is happening is that either the sequence generator has some cached sequence values, which would be lost during the hard restart. Or, one or more transactions in progress have retrieved a sequence value, but were interrupted before they were able to commit the transaction.

The documentation on sequence generation may help you identify how to set the size of your sequence cache, and what value to "restart" with.

In addition: In your situation, it sounds as though you may have a business rule requiring the primary keys in your table to be both sequential, and gapless (no gaps between sequences). A. Elein Mustain suggests a non-trivial solution to generating so called "gapless sequences".

Upvotes: 3

Related Questions