CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe. |
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 -
I don't know what this line means. You said it was stored in a relation, surely that needs to have happened through some command which preceded the commit chronologically, though formally they may have happened atomically.
"Later stored it in the table" - I'd have to double check with the other team, but IIUC it was application pseudo-code like this:
- execute SQL "select nextval()" and store result in my_local_variable_unique_id
- commit
- do some processing, tracing, logging, etc identified with my_local_variable_unique_id
- execute SQL "insert into mytable values(my_local_variable_unique_id, data1, data2)"
- commit
They weren't expecting that they could get duplicates from a sequence, which leads to unique violations and other problems later. Maybe a workaround is doing some kind of dummy insert or update or something in the transaction that gets a sequence value.
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.
Are you clarifying the original complaint, or this a new, different complaint? Vini's test cases don't include any insertions. Do you have test cases that can reproduce your complaint?
Clarification of same issue, not a new issue.
Tom also has said as much in his email - he said it's quite plausible that sequences used in isolation aren't crash safe. I just think we should document it; I'll work on a proposal/doc-update-patch for everyone to bikeshed on when I have a few minutes :)
-Jeremy
--
Jeremy Schneider
Database Engineer
Amazon Web Services