Re: Allow logical replication to copy tables in binary format - Mailing list pgsql-hackers

On Mon, Feb 27, 2023 at 2:31 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Feb 20, 2023 at 3:37 PM shiy.fnst@fujitsu.com
> <shiy.fnst@fujitsu.com> wrote:
> >
> > On Thu, Feb 16, 2023 8:48 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > 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?
> > >
> >
> > In the case that the data type doesn't have binary output function, for apply
> > phase, the column will be sent in text format (see logicalrep_write_tuple()) and
> > it works fine. But with copy_format = binary, the walsender exits with an
> > error.
> >
> ...
> ...
> >
> > Then I got the following error in the publisher log.
> >
> > walsender ERROR:  no binary output function available for type public.myvarchar
> > walsender STATEMENT:  COPY public.tbl1 (a) TO STDOUT  WITH (FORMAT binary)
> >
>
> Thanks for sharing the example. I think to address this user can
> create a SUBSCRIPTION with 'binary = false' and then after the initial
> copy enables it with ALTER SUBSCRIPTION.  Personally, I feel it is not
> required to have a separate option to allow copy in binary mode. Note,
> where there is some use for it but having more options for similar
> work is also confusing as users need to pay attention to different
> options and their values. It won't be difficult to add such an option
> in the future if we see such cases and or users specifically require
> something like this.

I agree with this thought, basically adding an extra option will
always complicate things for the user.  And logically it doesn't make
much sense to copy data in text mode and then stream in binary mode
(except in some exception cases and for that, we can always alter the
subscription).  So IMHO it makes more sense that if the binary option
is selected then ideally it should choose to do the initial sync also
in the binary mode.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com