Include sequence relation support in logical replication

Hi

From the PG logical replication documentation, I see that there is a listed limitation that sequence relation is not replicated logically. After some examination, I see that retrieving the next value from a sequence using the nextval() call will emits a WAL update every 32 calls to nextval(). In fact, when it emits a WAL update, it will write a future value 32 increments from now, and maintain a internal cache for delivering sequence numbers. It is done this way to minimize the write operation to WAL record at a risk of losing some values during a crash. So if we were to replicate the sequence, the subscriber will receive a future value (32 calls to nextval()) from now, and it obviously does not reflect current status. Sequence changes caused by other sequence-related SQL functions like setval() or ALTER SEQUENCE xxx, will always emit a WAL update, so replicating changes caused by these should not be a problem. 

I have shared a patch that allows sequence relation to be supported in logical replication via the decoding plugin ( test_decoding for example ); it does not support sequence relation in logical replication between a PG publisher and a PG subscriber via pgoutput plugin as it will require much more work. For the replication to make sense, the patch actually disables the WAL update at every 32 nextval() calls, so every call to nextval() will emit a WAL update for proper replication. This is done by setting SEQ_LOG_VALS to 0 in sequence.c

I think the question is that should we minimize WAL update frequency (every 32 calls) for getting next value in a sequence at a cost of losing values during crash or being able to replicate a sequence relation properly at a cost or more WAL updates?

Cary Huang

-------------

HighGo Software Inc. (Canada)

mailto:cary(dot)huang(at)highgo(dot)ca

http://www.highgo.ca