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

On Thu, Oct 14, 2021 at 8:50 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Thu, Oct 14, 2021 at 3:48 PM Sadhuprasad Patro <b.sadhu@gmail.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)

Regards,

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