Re: Include sequence relation support in logical replication

Hi,

On 2020-03-26 15:33:33 -0700, Cary Huang wrote:

> >> 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 
>
> >
>
> > Why is that needed? ISTM updating in increments of 32 is fine for
>
> > replication purposes? It's good imo, because sending out more granular
>
> > increments would increase the size of the WAL stream?
>
>
>
> yes, updating WAL at every 32 increment is good and have huge performance benefits according 
>
> to Michael, but when it is replicated logically to subscribers, the sequence value they receive would not
>
> make much sense to them.
>
> For example, 
>
>
>
> If i have a Sequence called "seq" with current value = 100 and increment = 5. The nextval('seq') call will
>
> return 105 to the client but it will write 260 to WAL record ( 100 + (5*32) ), because that is the value after 32
>
> increments and internally it is also maintaining a "log_cnt" counter that tracks how many nextval() calls have been invoked
>
> since the last WAL write, so it could kind of derive backwards to find the proper next value to return to client. 
>
>
>
> But the subscriber for this sequence will receive a change of 260 instead of 105, and it does not represent the current
>
> sequence status. Subscriber is not able to derive backwards because it does not know the increment size in its schema.

What is the problem with the subscriber seeing 260? This already can
happen on the primary today, if there is a crash / immediate
restart. But that is fine - sequences don't guarantee that they are free
of gaps, just that each call will return a bigger value than before.

>
> Setting SEQ_LOG_VALS to 0 in sequence.c basically disables this 32 increment behavior and makes WAL update at every nextval() call
>
> and therefore the subscriber to this sequence will receive the same value (105) as the client, as a cost of more WAL writes.
>
>
>
> I would like to ask if you have some suggestions or ideas that can make subscriber receives the current value without the need to
>
> disabling the 32 increment behavior?

It simply shouldn't expect that to be the case. What do you need it
for?

As far as I can tell replicating sequence values is useful to allow
failover, by ensuring all sequences will return sensible values going
forward. That does not require to now precise values.

Greetings,

Andres Freund