Re: logical decoding and replication of sequences, take 2

On 2/22/23 5:02 AM, Tomas Vondra wrote:
>
> On 2/22/23 03:28, Jonathan S. Katz wrote:

>> Thanks for continuing to work on this patch! I tested the latest version
>> and have some feedback/clarifications.
>>
>
> Thanks!

Also I should mention I've been testing with both async/sync logical
replication. I didn't have any specific comments on either as it seemed
to just work and behaviors aligned with existing expectations.

Generally it's been a good experience and it seems to be working. :) At
this point I'm trying to understand the limitations and tripwires so we
can guide users appropriately.

> Yes, this is due to how we WAL-log sequences. We don't log individual
> increments, but every 32nd increment and we log the "future" sequence
> state so that after a crash/recovery we don't generate duplicates.
>
> So you do nextval() and it returns 1. But into WAL we record 32. And
> there will be no WAL records until nextval reaches 32 and needs to
> generate another batch.
>
> And because logical replication relies on these WAL records, it inherits
> this batching behavior with a "jump" on recovery/failover. IMHO it's OK,
> it works for the "logical failover" use case and if you need gapless
> sequences then regular sequences are not an issue anyway.
>
> It's possible to reduce the jump a bit by reducing the batch size (from
> 32 to 0) so that every increment is logged. But it doesn't eliminate it
> because of rollbacks.

I generally agree. I think it's mainly something we should capture in
the user docs that they can be a jump on the subscriber side, so people
are not surprised.

Interestingly, in systems that tend to have higher rates of failover
(I'm thinking of a few distributed systems), this may cause int4
sequences to exhaust numbers slightly (marginally?) more quickly. Likely
not too big of an issue, but something to keep in mind.

>> 2. Using with origin=none with nonconflicting sequences.
>>
>> I modified the example in [1] to set up two schemas with non-conflicting
>> sequences[2], e.g. on instance 1:
>>
>> CREATE TABLE public.room (
>>     id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1)
>> PRIMARY KEY,
>>     name text NOT NULL
>> );
>>
>> and instance 2:
>>
>> CREATE TABLE public.room (
>>     id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2)
>> PRIMARY KEY,
>>     name text NOT NULL
>> );
>>
>
> Well, yeah. We don't support active-active logical replication (at least
> not with the built-in). You can easily get into similar issues without
> sequences.

The "origin=none" feature lets you replicate tables bidirectionally.
While it's not full "active-active", this is a starting point and a
feature for v16. We'll definitely have users replicating data
bidirectionally with this.

> Replicating a sequence overwrites the state of the sequence on the other
> side, which may result in it generating duplicate values with the other
> node, etc.

I understand that we don't currently support global sequences, but I am
concerned there may be a tripwire here in the origin=none case given
it's fairly common to use serial/GENERATED BY to set primary keys. And
it's fairly trivial to set them to be nonconflicting, or at least give
the user the appearance that they are nonconflicting.

From my high level understand of how sequences work, this sounds like
it would be a lift to support the example in [1]. Or maybe the answer is
that you can bidirectionally replicate the changes in the tables, but
not sequences?

In any case, we should update the restrictions in [2] to state: while
sequences can be replicated, there is additional work required if you
are bidirectionally replicating tables that use sequences, esp. if used
in a PK or a constraint. We can provide alternatives to how a user could
set that up, i.e. not replicates the sequences or do something like in [3].

Thanks,

Jonathan

[1] https://gist.github.com/jkatz/5c34bf1e401b3376dfe8e627fcd30af3
[2]
https://www.postgresql.org/docs/devel/logical-replication-restrictions.html
[3] https://gist.github.com/jkatz/1599e467d55abec88ab487d8ac9dc7c3