Re: logical decoding and replication of sequences, take 2

On 2/23/23 7:56 AM, Tomas Vondra wrote:
> On 2/22/23 18:04, Jonathan S. Katz wrote:
>> On 2/22/23 5:02 AM, Tomas Vondra wrote:
>>>

>> 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.
>>
>
> IMHO the number of systems that would work fine with int4 sequences but
> this change results in the sequences being "exhausted" too quickly is
> indistinguishable from 0. I don't think this is an issue.

I agree it's an edge case. I do think it's a number greater than 0,
having seen some incredibly flaky setups, particularly in distributed
systems. I would not worry about it, but only mentioned it to try and
probe edge cases.

>>> 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.
>>
>
> Well, then the users need to use some other way to generate IDs, not
> local sequences. Either some sort of distributed/global sequence, UUIDs
> or something like that.
[snip]

>> 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].
>>
>
> I agree. I see this as mostly a documentation issue.

Great. I agree that users need other mechanisms to generate IDs, but we
should ensure we document that. If needed, I'm happy to help with the
docs here.

Thanks,

Jonathan