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

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.

regards

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