Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

On Mon, 18 Oct 2021 at 11:26, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Thu, Oct 14, 2021 at 8:50 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>>
>> On Thu, Oct 14, 2021 at 3:48 PM Sadhuprasad Patro <b(dot)sadhu(at)gmail(dot)com> wrote:
>> >
>> > Hi All,
>> >
>> > Publisher 'DateStyle' is set as "SQL, MDY", whereas in Subscriber as
>> > "SQL, DMY", the logical replication is not working...
>> >
>> > From Publisher:
>> > postgres=# INSERT INTO calendar VALUES ('07-18-1036', '1'), ('05-15-1135', '1');
>> > INSERT 0 2
>> >
>> > Getting below error in the subscriber log file,
>> > 2021-10-14 00:59:23.067 PDT [38262] ERROR: date/time field value out
>> > of range: "07/18/1036"
>> > 2021-10-14 00:59:23.067 PDT [38262] HINT: Perhaps you need a
>> > different "datestyle" setting.
>> >
>> > Is this an expected behavior?
>>
>> Looks like a problem to me, I think for fixing this, on logical
>> replication connection always set subscriber's DateStlyle, with that
>> the walsender will always send the data in the same DateStyle that
>> worker understands and then we are good.
>
> +1
>
> Probably the same is true for IntervalStyle? If the publisher sets
> 'sql_standard', the subscriber sets 'postgres', and an interval value
> '-1 11:22:33' is inserted, these two nodes have different data:
>
> * Publisher
> =# set intervalstyle to 'postgres'; select * from test;
> i
> -------------------
> -1 days -11:22:33
> (1 row)
>
> * Subscriber
> =# set intervalstyle to 'postgres'; select * from test;
> i
> -------------------
> -1 days +11:22:33
> (1 row)
>

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.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.