Re: logical decoding and replication of sequences, take 2

On 7/14/23 15:50, Ashutosh Bapat wrote:
> On Fri, Jul 14, 2023 at 3:59 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
> 
>>
>>>>
>>>> The new patch detects that, and triggers ERROR on the publisher. And I
>>>> think that's the correct thing to do.
>>>
>>> With this behaviour users will never be able to setup logical
>>> replication between old and new servers considering almost every setup
>>> has sequences.
>>>
>>
>> That's not true.
>>
>> Replication to older versions works fine as long as the publication does
>> not include sequences (which need to be added explicitly). If you have a
>> publication with sequences, you clearly want to replicate them, ignoring
>> it is just confusing "magic".
> 
> I was looking at it from a different angle. Publishers publish what
> they want, subscribers choose what they want and what gets replicated
> is intersection of these two sets. Both live happily.
> 
> But I am fine with that too. It's just that users need to create more
> publications.
> 

I think you might make essentially the same argument about replicating
just some of the tables in the publication. That is, the publication has
tables t1 and t2, but subscriber only has t1. That will fail too, we
don't allow the subscriber to ignore changes for t2.

I think it'd be rather weird (and confusing) to do this differently for
different types of replicated objects.

>>
>> If you have a publication with sequences and still want to replicate to
>> an older server, create a new publication without sequences.
>>
> 
> I tested the current patches with subscriber at PG 14 and publisher at
> master + these patches. I created one table and a sequence on both
> publisher and subscriber. I created two publications, one with
> sequence and other without it. Both have the table in it. When the
> subscriber subscribes to the publication with sequence, following
> ERROR is repeated in the subscriber logs and nothing gets replicated
> ```
> [2023-07-14 18:55:41.307 IST] [916293] [] [] [3/30:0] LOG:  00000:
> logical replication apply worker for subscription "sub5433" has
> started
> [2023-07-14 18:55:41.307 IST] [916293] [] [] [3/30:0] LOCATION:
> ApplyWorkerMain, worker.c:3169
> [2023-07-14 18:55:41.322 IST] [916293] [] [] [3/0:0] ERROR:  08P01:
> could not receive data from WAL stream: ERROR:  protocol version does
> not support sequence replication
>     CONTEXT:  slot "sub5433", output plugin "pgoutput", in the
> sequence callback, associated LSN 0/1513718
> [2023-07-14 18:55:41.322 IST] [916293] [] [] [3/0:0] LOCATION:
> libpqrcv_receive, libpqwalreceiver.c:818
> [2023-07-14 18:55:41.325 IST] [916213] [] [] [:0] LOG:  00000:
> background worker "logical replication worker" (PID 916293) exited
> with exit code 1
> [2023-07-14 18:55:41.325 IST] [916213] [] [] [:0] LOCATION:
> LogChildExit, postmaster.c:3737
> ```
> 
> When the subscriber subscribes to the publication without sequence,
> things work normally.
> 
> The cross-version replication is working as expected then.
> 

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


regards

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