Re: Sequence's value can be rollback after a crashed recovery. - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: Sequence's value can be rollback after a crashed recovery.
Date
Msg-id 0e8320e1-a18e-9fb2-1624-b3beb18fcb17@enterprisedb.com
Whole thread Raw
In response to Re: Sequence's value can be rollback after a crashed recovery.  (Dilip Kumar <dilipbalaut@gmail.com>)
Responses Re: Sequence's value can be rollback after a crashed recovery.
Re: Sequence's value can be rollback after a crashed recovery.
List pgsql-hackers
On 11/23/21 5:22 AM, Dilip Kumar wrote:
> On Tue, Nov 23, 2021 at 9:30 AM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
> 
>> On 11/22/21 9:42 PM, Jeremy Schneider wrote:
>>> On 11/22/21 12:31, Tom Lane wrote:
>>>> "Bossart, Nathan" <bossartn@amazon.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



pgsql-hackers by date:

Previous
From: Paul Martinez
Date:
Subject: Re: [PATCH] Partial foreign key updates in referential integrity triggers
Next
From: Bharath Rupireddy
Date:
Subject: pg_replslotdata - a tool for displaying replication slot information