Re: logical decoding and replication of sequences, take 2 - Mailing list pgsql-hackers

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