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

On 11/23/21 05:49, Andy Fan wrote:
>
> > 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
> <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 agree with this,  the commit would flush the xlog and persist the change. 

On that older thread, there were exact reproductions in the first email
from Vini - two of them - available here:

https://gist.github.com/vinnix/2fe148e3c42e11269bac5fcc5c78a8d1

Nathan help me realize a mistake I've made here.

The second reproduction involved having psql run nextval() inside of an
explicit transaction. I had assumed that the transaction would be
committed when psql closed the session without error. This is because in
Oracle SQLPlus (my original RDBMS background), the "exitcommit" setting
has a default value giving this behavior.

This was a silly mistake on my part. When PostgreSQL psql closes the
connection with an open transaction, it turns out that the PostgreSQL
server will abort the transaction rather than committing it. (Oracle
DBAs be-aware!)

Nonetheless, Vini's first reproduction did not make this same mistake.
It involved 10 psql sessions in parallel using implicit transactions,
suspending I/O (via the linux device mapper), and killing PG while the
I/O is suspended.

Given my mistake on the second repro, I want to look a little closer at
this first reproduction and revisit whether it's actually demonstrating
a corner case where one could claim that durability isn't being handled
correctly - that "COMMIT" is returning successfully to the application,
and yet the sequence numbers are being repeated. Maybe there's something
involving the linux I/O path coming into play here.

-Jeremy

--
http://about.me/jeremy_schneider