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

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

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com