On Mon, 18 Oct 2021 at 17:27, Dilip Kumar <dilipbal...@gmail.com> wrote:
> On Mon, Oct 18, 2021 at 1:41 PM Japin Li <japi...@hotmail.com> wrote:
>
>> 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.
>
> I think the idea of setting the standard DateStyle and the
> IntervalStyle on the walsender process looks fine to me. As this will
> avoid extra network round trips as Tom mentioned.
After some test, I find we also should set the extra_float_digits to avoid
precision lossing.
Please consider the v4 patch to review.
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index e2a76ba055..7ae0c7bff2 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2223,6 +2223,24 @@ retry1:
{
am_walsender = true;
am_db_walsender = true;
+
+ /*
+ * Force assorted GUC parameters to settings that ensure
+ * that we'll output data values in a form that is
+ * unambiguous to the walreceiver.
+ */
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("datestyle"));
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("ISO"));
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("intervalstyle"));
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("postgres"));
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("extra_float_digits"));
+ port->guc_options = lappend(port->guc_options,
+ pstrdup("3"));
}
else if (!parse_bool(valptr, &am_walsender))
ereport(FATAL,
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index baa4a90771..d92ab60d86 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 => 7;
# Bug #15114
@@ -224,3 +224,69 @@ $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->append_conf('postgresql.conf',
+ "extra_float_digits = '-4'");
+$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;
+
+# Table for datestyle
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_rep(a date)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_rep(a date)");
+
+# Table for extra_float_digits
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE flt_rep(a real, d double precision)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE flt_rep(a real, d double precision)");
+
+# 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->safe_psql('postgres',
+ "INSERT INTO tab_rep VALUES ('07-18-2021'), ('05-15-2018')");
+
+$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');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO flt_rep VALUES (1.2121323, 32.32321232132434)");
+
+$node_publisher->wait_for_catchup('tab_sub');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT a, d FROM flt_rep");
+is($result, qq(1.2121323|32.32321232132434),
+ 'failed to replication floating-point values');
+
+# 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->safe_psql('postgres', 'DROP TABLE flt_rep');
+$node_subscriber->safe_psql('postgres', 'DROP TABLE flt_rep');
+
+# Drop subscription/publication as we don't need anymore
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tab_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tab_pub');
+
+$node_publisher->stop('fast');
+$node_subscriber->stop('fast');
