On Sat, 23 Oct 2021 at 00:55, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Japin Li <japi...@hotmail.com> writes:
>> 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).
>
> You could make it a little less confusing by not insisting on a
> space in the datestyle. This should work fine:
>
> vals[i] = "-c datestyle=ISO,YMD -c intervalstyle=postgres
> extra_float_digits=3";
>
Oh. My apologies. I try this style before, but find it see "ISO," is not valid,
so I add backslash, but it seems like that is my environment doesn't cleanup.
Fixed.
> Also, I think some comments would be appropriate.
>
Add comments for it.
> I don't see any value whatsoever in the more complicated version
> of the patch. It's just more code to maintain and more things
> to go wrong. And not only at our level, but the DBA's too.
Agreed.
> What if the subscriber and publisher are of different PG versions
> and have different ideas of the valid values of these settings?
>
Sorry, I'm a bit confused. Do you mean we should provide a choose for user
to set thoses parameters when establish logical replication?
--
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..2c1a598300 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,13 @@ libpqrcv_connect(const char *conninfo, bool logical, const char *appname,
{
keys[++i] = "client_encoding";
vals[i] = GetDatabaseEncodingName();
+
+ /*
+ * Force assorted GUC parameters to settings that ensure that we'll output
+ * data values in a form that is unambiguous to the publisher.
+ */
+ keys[++i] = "options";
+ vals[i] = "-c datestyle=ISO,YMD -c intervalstyle=postgres -c extra_float_digits=3";
}
keys[++i] = NULL;
vals[i] = NULL;
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');
