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

On Fri, 22 Oct 2021 at 15:00, Sadhuprasad Patro <b(dot)sadhu(at)gmail(dot)com> wrote:
> On Fri, Oct 22, 2021 at 8:07 AM Japin Li <japinli(at)hotmail(dot)com> wrote:
>>
>>
>> On Fri, 22 Oct 2021 at 08:26, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> > On Thu, Oct 21, 2021 at 11:18 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>> >>
>> >> 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?
>> >
>>
>> Yeah, it might be break the compatibility.
>>
>> In conclusion, this bug has two ways to fix.
>>
>> In conclusion, this bug has two ways to fix.
>>
>> 1. Set the parameters on publisher, this might be break the compatibility.
>
> Is it not possible to set the parameter on publisher as "ISO, MDY" or
> "ISO, YMD", instead of only "ISO"?
> DateStyle includes both, so we may set the parameter with date format...
>
>> 2. Set the parameters on subscriber. In my first patch, I try to set the
>> parameters after establish the connection, it will lead more network
>> round trips. We can set the parameters when connecting the walsender
>> using "options".
>>
>> For the second way, should we set the parameters same as subscriber or
>> use the parameters (e.g. datestyle = "ISO") likes postgres_fdw
>> set_transmission_modes()?
>>
>> Any thoughts?
>
> IMO, setting the parameter value same as the subscriber is better. It
> is always possible that we can set any datestyle in the plugins
> itself...
>

Attach v5 patch. This patch set the datestyle, intervalstyle and
extra_float_digits parameters when we connect to publisher, this can
avoid the network round trips (compare with the first patch).

OTOH, the patch uses the subscriber's parameters as connecting parameters,
which is more complex. If we use the parameters likes postgres_fdw
set_transmission_mode(), the code will be easier [1].

[1]
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 5c6e56a5b2..0d03edd39f 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -128,8 +128,8 @@ libpqrcv_connect(const char *conninfo, bool logical, const char *appname,
{
WalReceiverConn *conn;
PostgresPollingStatusType status;
- const char *keys[5];
- const char *vals[5];
+ const char *keys[6];
+ const char *vals[6];
int i = 0;

/*
@@ -155,6 +155,8 @@ libpqrcv_connect(const char *conninfo, bool logical, const char *appname,
{
keys[++i] = "client_encoding";
vals[i] = GetDatabaseEncodingName();
+ keys[++i] = "options";
+ vals[i] = "-c datestyle=ISO,\\ YMD -c intervalstyle=postgres extra_float_digits=3";
}
keys[++i] = NULL;
vals[i] = NULL;

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