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

Hi:

Should we guarantee the sequence's nextval should never be rolled back
even in a crashed recovery case?
I can produce the rollback in the following case:

Session 1:
CREATE SEQUENCE s;
BEGIN;
SELECT nextval('s'); \watch 0.01

Session 2:
kill -9 {sess1.pid}

After the restart, the  nextval('s') may be rolled back (less than the
last value  from session 1).

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].

-- 
Best Regards
Andy Fan