Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber - Mailing list pgsql-hackers

On Thu, Oct 21, 2021 at 11:18 PM Japin Li <japinli@hotmail.com> wrote:
>
>
> On Thu, 21 Oct 2021 at 19:54, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > On Thu, Oct 21, 2021 at 3:04 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >>
> >> On Thu, Oct 21, 2021 at 11:16 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >> >
> >> > On Wed, Oct 20, 2021 at 8:12 PM Japin Li <japinli@hotmail.com> wrote:
> >> > >
> >> > >
> >> > > On Mon, 18 Oct 2021 at 17:27, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >> > > > On Mon, Oct 18, 2021 at 1:41 PM Japin Li <japinli@hotmail.com> wrote:
> >> > > >
> >> > > >> I attached v3 patch that set IntervalStyle to 'postgres' when the
> >> > > >> server backend is walsender, and this problem has gone.
> >> > > >
> >> > > >> I test that set IntervalStyle to 'sql_standard' on publisher and
> >> > > >> 'iso_8601' on subscriber, it works fine.
> >> > > >
> >> > > >> Please try v3 patch and let me know if they work as unexpected.
> >> > > >> Thanks in advance.
> >> > > >
> >> > > > I think the idea of setting the standard DateStyle and the
> >> > > > IntervalStyle on the walsender process looks fine to me.  As this will
> >> > > > avoid extra network round trips as Tom mentioned.
> >> > >
> >> > > After some test, I find we also should set the extra_float_digits to avoid
> >> > > precision lossing.
> >> >
> >> > I'm concerned that it sets parameters too early since wal senders end
> >> > up setting the parameters regardless of logical decoding plugins. It
> >> > might be better to force the parameters within the plugin for logical
> >> > replication, pgoutput, in order to avoid affecting other plugins? On
> >> > the other hand, if we do so, we will need to handle table sync worker
> >> > cases separately since they copy data via COPY executed by the wal
> >> > sender process. For example, we can have table sync workers set the
> >> > parameters.
> >>
> >> You mean table sync worker to set over the replication connection
> >> right?  I think that was the first solution where normal workers, as
> >> well as table sync workers, were setting over the replication
> >> connection, but Tom suggested that setting on the walsender is a
> >> better option as we can avoid the network round trip.
> >
> > Right.
> >
> > BTW I think we can set the parameters from the subscriber side without
> > additional network round trips by specifying the "options" parameter
> > in the connection string, no?
> >
>
> Yes, we can.  However, each client should be concerned the style for
> datestyle, IMO it is boring.
>
> >> If we want to set it over the replication connection then do it for
> >> both as Japin's first patch is doing, otherwise, I am not seeing any
> >> big issue in setting it early in the walsender also.  I think it is
> >> good to let walsender always send in the standard format which can be
> >> understood by other node, no?
> >
> > Yeah, probably the change on HEAD is fine but I'm a bit concerned
> > about possible issues on back branches like if the user expects to get
> > date data in the style of DateStyle setting on the server via
> > pg_recvlogical, this change could break it.
> >
>
> How it breaks?

I don't know the real case but for example, if an application gets
changes via pg_recvlogical with a decoding plugin (say wal2json) from
the database whose DateStyle setting is "SQL, MDY", it expects that
the date values in the streamed data are in the style of "ISO, MDY".
But with this change, it will get date values in the style of "ISO"
which could lead to a parse error in the application.

>  The user also can specify the "options" to get date data
> in the style which they are wanted. Right?

Right. But doesn't it mean breaking the compatibility?

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/