Re: SEQUENCE values (duplicated) in some corner cases when crashhappens

On 4/29/20 11:02, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> On 2020-Apr-29, Vinicius Abrahao wrote:
>>> I'm opening this thread to clarify something that I can observe: duplicated
>>> values for sequences.
> 
>> I'm not sure that a sequence that produces the same value twice, without
>> writing it to the database the first time, and with an intervening crash
>> in between, is necessarily a bug that we care to fix.  Especially so if
>> the fix will cause a large performance regression for the normal case
>> where the sequence value is written to the DB by a committed transaction.
> 
> I believe this behavior is 100% intentional: the advance of the sequence
> value is logged to WAL, but we don't guarantee to make the WAL entry
> persistent until the calling transaction commits.  And while I'm too
> lazy to check right now, I think the calling transaction might've had
> to cause some additional non-sequence-object updates to happen on disk,
> too, else we won't think it has done anything that needs committing.
The behavior we're observing is that a nextval() call in a committed
transaction is not crash-safe. This was discovered because some
applications were using nextval() to get a guaranteed unique sequence
number [or so they thought], then the application did some processing
with the value and later stored it in a relation of the same database.

The nextval() number was not used until the transaction was committed -
but then the fact of a value being generated, returned and committed was
lost on crash. The nextval() call used in isolation did not seem to
provide durability.


> As you say, doing something different would entail a large performance
> penalty for a rather dubious semantic requirement.  The normal expectation
> is that we have to protect sequence values that get written into tables
> someplace.

Whether or not it's dubious is in the eye of the beholder I guess; in
Oracle I believe the equivalent use of sequences provides the usual
durability guarantees. Probably that's why this particular user was
surprised at the behavior. If PostgreSQL isn't going to provide
durability for isolated use of sequences, then IMO that's fine but the
fact should at least be in the documentation.

-Jeremy


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services