Re: Sequence's value can be rollback after a crashed recovery.

On 11/23/21 5:22 AM, Dilip Kumar wrote:
> On Tue, Nov 23, 2021 at 9:30 AM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
>> On 11/22/21 9:42 PM, Jeremy Schneider wrote:
>>> On 11/22/21 12:31, Tom Lane wrote:
>>>> "Bossart, Nathan" <bossartn(at)amazon(dot)com> writes:
>>>>> I periodically hear rumblings about this behavior as well. At the
>>>>> very least, it certainly ought to be documented if it isn't yet. I
>>>>> wouldn't mind trying my hand at that. Perhaps we could also add a new
>>>>> configuration parameter if users really want to take the performance
>>>>> hit.
>>>>
>>>> A sequence's cache length is already configurable, no?
>>>>
>>>
>>> Cache length isn't related to the problem here.
>>>
>>> The problem is that PostgreSQL sequences are entirely unsafe to use from
>>> a durability perspective, unless there's DML in the same transaction.
>>>
>>> Users might normally think that "commit" makes things durable.
>>> Unfortunately, IIUC, that's not true for sequences in PostgreSQL.
>>>
>>
>> That's not what the example in this thread demonstrates, though. There's
>> no COMMIT in that example, so it shows that we may discard the nextval()
>> in uncommitted transactions. I fail to see how that's less durable than
>> any other DML (e.g. we don't complain about INSERT not being durable if
>> you don't commit the change).
>>
>> If you can show that the sequence goes back after a commit, that'd be an
>> actual durability issue.
>
> I think at this thread[1], which claimed to get this issue even after
> commit, I haven't tried it myself though.
>
> [1] https://www.postgresql.org/message-id/flat/ea6485e3-98d0-24a7-094c-87f9d5f9b18f%40amazon.com#4cfe7217c829419b769339465e8c2915
>

I did try, and I haven't been able to reproduce that behavior (on
master, at least).

I see Tom speculated we may not flush WAL if a transaction only does
nextval() in that other thread, but I don't think that's true. AFAICS if
the nextval() call writes stuff to WAL, the RecordTransactionCommit will
have wrote_xlog=true and valid XID. And so it'll do the usual usual
XLogFlush() etc.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company