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.
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?
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With