Re: logical decoding and replication of sequences, take 2

On Fri, Jul 14, 2023 at 3:59 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)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.

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

--
Best Wishes,
Ashutosh Bapat