MultiWriterStreamIdGenerator: wrong behaviour when using sequences initialised with `START WITH`

[reivilibre]

All of Synapse's pre-existing sequences have been initialised using setval('stream_name', <biggest persisted value>). This makes sense because the sequence was created after the data that was streamed (as far as I understand from Synapse's history).

Important semantics:

CREATE SEQUENCE seq;
SELECT setval('seq', 2);
SELECT last_value FROM seq; -- gives 2
SELECT nextval('seq'); -- gives 3

However, for new streams, we need to override the start position so it doesn't start at 1. (If it starts at 1, then Synapse ignores the value persisted here, since all stream readers start off by assuming position 1 has been filled.)

The most natural way to do this is

CREATE SEQUENCE seq START WITH 2;

This however has a sneaky critical problem, in that the semantics of last_value are different:

CREATE SEQUENCE seq START WITH 2;
SELECT last_value FROM seq; -- gives 2! but 2 hasn't been persisted yet
SELECT nextval('seq'); -- gives 2

Since Synapse uses last_value on startup to find the persisted-upto positions, this means that for an empty stream whose sequence was created with START WITH 2, the readers will also advance their token to position 2 before position 2 has been persisted.

[reivilibre]

The best answer for new streams seems to be to nextval them as soon as they are created:

CREATE SEQUENCE seq;
SELECT nextval('seq');

This seems to give the right semantics:

postgres=# SELECT last_value FROM seq;
 last_value 
------------
          1
(1 row)

postgres=# SELECT nextval('seq');
 nextval 
---------
       2
(1 row)