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

On Wed, Oct 20, 2021 at 8:12 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>
>
> On Mon, 18 Oct 2021 at 17:27, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > On Mon, Oct 18, 2021 at 1:41 PM Japin Li <japinli(at)hotmail(dot)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.

Thank you for the patch!

--- 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"));
}

I'm concerned that it sets parameters too early since wal senders end
up setting the parameters regardless of logical decoding plugins. It
might be better to force the parameters within the plugin for logical
replication, pgoutput, in order to avoid affecting other plugins? On
the other hand, if we do so, we will need to handle table sync worker
cases separately since they copy data via COPY executed by the wal
sender process. For example, we can have table sync workers set the
parameters.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/