Re: Allow logical replication to copy tables in binary format

Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, 16 Şub 2023 Per, 15:47 tarihinde
şunu yazdı:

> On Mon, Jan 30, 2023 at 4:19 PM Melih Mutlu <m(dot)melihmutlu(at)gmail(dot)com>
> wrote:
> >> 8. Note that the COPY binary format isn't portable across platforms
> >> (Windows to Linux for instance) or major versions
> >> https://www.postgresql.org/docs/devel/sql-copy.html, whereas, logical
> >> replication is
> https://www.postgresql.org/docs/devel/logical-replication.html.
> >> I don't see any handling of such cases in copy_table but only a check
> >> for the publisher version. I think we need to account for all the
> >> cases - allow binary COPY only when publisher and subscriber are of
> >> same versions, architectures, platforms. The trick here is how we
> >> identify if the subscriber is of the same type and taste
> >> (architectures and platforms) as the publisher. Looking for
> >> PG_VERSION_STR of publisher and subscriber might be naive, but I'm not
> >> sure if there's a better way to do it.
> >
> >
> > I think having the "copy_format" option with text as default, like I
> replied to your 3rd review above, will keep things as they are now.
> > The patch now will only allow users to choose binary copy as well, if
> they want it and acknowledge the limitations that come with binary copy.
> > COPY command's portability issues shouldn't be an issue right now, since
> the patch still supports text format. Right?
> >
>
> One thing that is not completely clear from above is whether we will
> have any problem if the subscription uses binary mode for copying
> across the server versions. Do we use binary file during the copy used
> in logical replication?
>

Since binary copy relies on COPY command, we may have problems across
different server versions in cases where COPY is not portable.
What I understand from this [1], COPY works across server versions later
than 7.4. This shouldn't be a problem for logical replication.
Currently the patch also does not allow binary copy if the publisher
version is older than 16.

> > Logical replication between different types like int and smallint is
already not
> working properly on HEAD too.
> > Yes, the scenario you shared looks like working. But you didn't create
the
> subscription with binary=true. The patch did not change subscription with
> binary=false case. I believe what you should experiment is binary=true
case
> which already fails in the apply phase on HEAD.
> >
> > Well, with this patch, it will begin to fail in the table copy phase.
But I don't
> think this is a problem because logical replication in binary format is
already
> broken for replications between different data types.
> >
>

> So, doesn't this mean that there is no separate failure mode during
> the initial copy? I am clarifying this to see if the patch really
> needs a separate copy_format option for initial sync?
>

It will fail in a case such as [2] while it would work on HEAD.
What I meant by my above comment was that binary enabled subscriptions are
not already working properly if they replicate between different types. So,
the failure caused by replicating, for example, from smallint to int is not
really introduced by this patch. Such subscriptions would fail in
apply phase anyway. With this patch they will fail while binary copy.

[1] https://www.postgresql.org/docs/current/sql-copy.html
[2]
https://www.postgresql.org/message-id/OSZPR01MB6310B58F069FF8E148B247FDFDA49%40OSZPR01MB6310.jpnprd01.prod.outlook.com

Best,
--
Melih Mutlu
Microsoft