I'm opening this thread to clarify something that I can observe: duplicated values for sequences.
[My understanding is that duplication is not something we desire. In fact it does NOT happen in the majority of cases, for example, when you immediately insert the value and commit it. But it can eventually happen in some specific scenarios...describe below]
The duplication can be observed when you only makes use of "nextval" (which calls sequence.c / nextval_internal function) without - inserting it for any reason - and the database crashes.
This is reproducible using the steps described on this link:
https://gist.github.com/vinnix/2fe148e3c42e11269bac5fcc5c78a8d1
There are two variants where this is reproducible (steps on the link above):
- Autocommit + Suspending I/O (simulating a storage issue)
- Explicitly opening transaction + Not Suspending I/O
** To simulate the "crash" I'm running: `killall -9 postgres`.
I've being debugging sequence.c code, I can see we only want to flush the WAL segments once every 32 requests (per connection) - this is defined by SEQ_LOG_VALS. Since, these values are "persisted" in the WAL file containing the "advanced"/future values; not the current value being retrieved by nextval_internal().
What I'm trying to understand now if this is a "bug" or a "new feature"...
Kind regards,