Re: logical decoding and replication of sequences, take 2

On Fri, Jul 14, 2023 at 7:33 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:

>
> Thanks for testing / confirming this! So, do we agree this behavior is
> reasonable?
>

This behaviour doesn't need any on-disk changes or has nothing in it
which prohibits us from changing it in future. So I think it's good as
a v0. If required we can add the protocol option to provide more
flexible behaviour.

One thing I am worried about is that the subscriber will get an error
only when a sequence change is decoded. All the prior changes will be
replicated and applied on the subscriber. Thus by the time the user
realises this mistake, they may have replicated data. At this point if
they want to subscribe to a publication without sequences they will
need to clean the already replicated data. But they may not be in a
position to know which is which esp when the subscriber has its own
data in those tables. Example,

publisher: create publication pub with sequences and tables
subscriber: subscribe to pub
publisher: modify data in tables and sequences
subscriber: replicates some data and errors out
publisher: delete some data from tables
publisher: create a publication pub_tab without sequences
subscriber: subscribe to pub_tab
subscriber: replicates the data but rows which were deleted on
publisher remain on the subscriber

--
Best Wishes,
Ashutosh Bapat