Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres Sequence jumping by 32

We use sequences to maintain the number of orders at a particular business unit. Last few we days, we have noticed that there have been has strange jumps ranging from 1 to 32 in the sequence numbers, multiple times a day. The sequence which we have configured has a cache_value of 1. Why is this happening and how can we resolve this? I couldn't find much documentation regarding the same.

like image 447
divyesh Avatar asked Jul 21 '16 14:07

divyesh


People also ask

Is Postgres sequence thread safe?

Yes it is threadsafe. Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. Do they assume clients are from different machines?

How sequence works in Postgres?

In PostgreSQL, a sequence is a user-defined schema-bound object which creates a sequence of integers depending on the particular requirement. In PostgreSQL sequence, the orders of numbers are important. Such as {5,6,7,8,9,10} and {10,9,8,7,6,5} are completely different sequences.

Does Postgres support sequence?

A sequence in PostgreSQL is a user-defined schema-bound object that yields a sequence of integers based on a specified specification. The CREATE SEQUENCE statement is used to create sequences in PostgreSQL.


1 Answers

PostgreSQL sequences can jump by 32 when you promote a secondary server to primary, and sometimes when PostgreSQL crashes hard and has to recover.

I have only seen it skip 32, and I haven't been able to change it by changing the sequence's cache value.

https://www.postgresql.org/message-id/1296642753.8673.29.camel%40gibralter, and the response https://www.postgresql.org/message-id/20357.1296659633%40sss.pgh.pa.us, indicate that this is expected behavior in a crash condition. I'm unsure if this only occurs if you're using Streaming Replication or not.

I thought it was a safety feature, in case the primary had committed records that had not been replicated to the secondary at the time of promotion. Then a human would be able to manually dump and copy the records from the old-primary to the new-primary before wiping the old-primary.

This could indicate that your primary is crashing and recovering several times per day, which is not a good state to be in.

like image 132
Craig Lewis Avatar answered Sep 19 '22 15:09

Craig Lewis