Re: logical decoding and replication of sequences, take 2

On 7/18/23 15:52, Ashutosh Bapat wrote:
> 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.
>

True, although "no on-disk changes" does not exactly mean we can just
change it at will. Essentially, once it gets released, the behavior is
somewhat fixed for the next ~5 years, until that release gets EOL. And
likely longer, because more features are likely to do the same thing.

That's essentially why the patch was reverted from PG16 - I was worried
the elaborate protocol versioning/negotiation was not the right thing.

> 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
>

Sure, but I'd argue that's correct. If the replication stream has
something the subscriber can't apply, what else would you do? We had
exactly the same thing with TRUNCATE, for example (except that it failed
with "unknown message" on the subscriber).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company