On Sat, 16 Oct 2021 at 22:42, Japin Li <[email protected]> wrote:
> On Thu, 14 Oct 2021 at 19:49, Dilip Kumar <[email protected]> wrote:
>> On Thu, Oct 14, 2021 at 3:48 PM Sadhuprasad Patro <[email protected]> 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.
Add a test case in subscription/t/100_bugs.pl. Please consider the v2 patch
for review.
--
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;
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index baa4a90771..a88a61df41 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -6,7 +6,7 @@ use strict;
use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 5;
+use Test::More tests => 6;
# Bug #15114
@@ -224,3 +224,44 @@ $node_sub->safe_psql('postgres', "DROP TABLE tab1");
$node_pub->stop('fast');
$node_pub_sub->stop('fast');
$node_sub->stop('fast');
+
+# Verify different datestyle between publisher and subscriber.
+$node_publisher = PostgresNode->new('datestyle_publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->append_conf('postgresql.conf',
+ "datestyle = 'SQL, MDY'");
+$node_publisher->start;
+
+$node_subscriber = PostgresNode->new('datestyle_subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->append_conf('postgresql.conf',
+ "datestyle = 'SQL, DMY'");
+$node_subscriber->start;
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_rep(a date)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_rep VALUES ('07-18-2021'), ('05-15-2018')");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_rep(a date)");
+
+# Setup logical replication
+my $node_publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tab_pub FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tab_sub CONNECTION '$node_publisher_connstr' PUBLICATION tab_pub");
+
+$node_publisher->wait_for_catchup('tab_sub');
+
+my $result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM tab_rep");
+is($result, qq(2), 'failed to replication date from different datestyle');
+
+# Clean up the tables on both publisher and subscriber as we don't need them
+$node_publisher->safe_psql('postgres', 'DROP TABLE tab_rep');
+$node_subscriber->safe_psql('postgres', 'DROP TABLE tab_rep');
+
+$node_publisher->stop('fast');
+$node_subscriber->stop('fast');
