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

[Japin Li]

On Thu, 14 Oct 2021 at 19:49, Dilip Kumar <dilipbal...@gmail.com> wrote:
> On Thu, Oct 14, 2021 at 3:48 PM Sadhuprasad Patro <b.sa...@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.

Right! Attached fix it.

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


diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 5c6e56a5b2..81cf9e30d7 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -30,6 +30,7 @@
 #include "pqexpbuffer.h"
 #include "replication/walreceiver.h"
 #include "utils/builtins.h"
+#include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/pg_lsn.h"
 #include "utils/tuplestore.h"
@@ -218,6 +219,7 @@ libpqrcv_connect(const char *conninfo, bool logical, const char *appname,
 	if (logical)
 	{
 		PGresult   *res;
+		const char *datestyle;
 
 		res = libpqrcv_PQexec(conn->streamConn,
 							  ALWAYS_SECURE_SEARCH_PATH_SQL);
@@ -229,6 +231,23 @@ libpqrcv_connect(const char *conninfo, bool logical, const char *appname,
 							pchomp(PQerrorMessage(conn->streamConn)))));
 		}
 		PQclear(res);
+
+		datestyle = GetConfigOption("datestyle", true, true);
+		if (datestyle != NULL) {
+			char *sql;
+
+			sql = psprintf("SELECT pg_catalog.set_config('datestyle', '%s', false);", datestyle);
+			res = libpqrcv_PQexec(conn->streamConn, sql);
+			if (PQresultStatus(res) != PGRES_TUPLES_OK)
+			{
+				PQclear(res);
+				ereport(ERROR,
+						(errmsg("could not set datestyle: %s",
+								pchomp(PQerrorMessage(conn->streamConn)))));
+			}
+			PQclear(res);
+			pfree(sql);
+		}
 	}
 
 	conn->logical = logical;