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.hu...@highgo.ca http://www.highgo.ca
sequence_replication.patch
Description: Binary data
