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

On Mon, 2021-11-22 at 15:43 +0800, Andy Fan wrote:
> > > The reason is because we never flush the xlog for the nextval_internal
> > > for the above case.  So if
> > > the system crashes, there is nothing to redo from. It can be fixed
> > > with the following online change
> > > code.
> > >
> > > @@ -810,6 +810,8 @@ nextval_internal(Oid relid, bool check_permissions)
> > >                  recptr = XLogInsert(RM_SEQ_ID, XLOG_SEQ_LOG);
> > >
> > >                  PageSetLSN(page, recptr);
> > > +
> > > +               XLogFlush(recptr);
> > >          }
> > >
> > >
> > > If a user uses sequence value for some external systems,  the
> > > rollbacked value may surprise them.
> > > [I didn't run into this issue in any real case,  I just studied xlog /
> > > sequence stuff today and found this case].
> >
> > I think that is a bad idea.
> > It will have an intolerable performance impact on OLTP queries, doubling
> > the number of I/O requests for many cases.
>
> The performance argument was expected before this writing. If we look at the
> nextval_interval more carefully, we can find it would not flush the xlog every
> time even the sequence's cachesize is 1. Currently It happens every 32 times
> on the nextval_internal at the worst case.

Right, I didn't think of that. Still, I'm -1 on this performance regression.

Yours,
Laurenz Albe