Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: [PATCH] Support automatic sequence replication
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <TY4PR01MB169072DEE7CC20E9B06F0164C947DA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
Date: Fri, 6 Mar 2026 16:52:28 +0800
Cc: shveta malik <shveta.malik@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
 Ajin Cherian <itsajin@gmail.com>,
 "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 Ashutosh Sharma <ashu.coek88@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C860270A-26B8-46F3-9A01-4377B8BE9D5D@gmail.com>
References: 
 <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
 <CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC=T0ZFLA@mail.gmail.com>
 <CAFPTHDZg1JrunGgOj332hr+gUuH_Jm7skqPpYSvd-QE3yEdRDQ@mail.gmail.com>
 <CAJpy0uBz7MCSUkvFJD9ij65vBahNmY+bfCgdGKRqXovYs+K_TA@mail.gmail.com>
 <CAJpy0uDsuNqjWd-TmGBxqSS1rnVCJ3B8=SYrtxQ=Vs8kb71QFA@mail.gmail.com>
 <CAJpy0uAMWg3KcXtVBS7B0rnchLNrCCVYBByJCzAp=u5LERgtfA@mail.gmail.com>
 <CAFPTHDZwEhxhDAeqcPi0GuYN6xBs8gFXHOMUnbg3u2Xigcz4Zg@mail.gmail.com>
 <CAE9k0PmTyCU1A9YEf+MRgfeZ8yK1bAYJu=o0bH8DNUTzXejQyQ@mail.gmail.com>
 <CAA4eK1L6czEzG4mLNZSyjYC5nX0FMSjjk3csKuxPD3Ph5-7Yvw@mail.gmail.com>
 <CAJpy0uAhGQJ=msVsn2GsqWXr+YESJK6x9NBvrUtKvtvp1OVuKQ@mail.gmail.com>
 <CAJpy0uAOuu-M6wobH2wHOdTymm-cX9+MqwPyRNoOt=sPKBdCew@mail.gmail.com>
 <CAFPTHDZiWYXoKoo4VcBYNH9a=gxDZhfkcBeXt5w6cLw4_ysyKw@mail.gmail.com>
 <OS9PR01MB12149D9054CC7F2DC3F0D26A1F577A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CAA4eK1KYxQALt46k5uWOO6SUtNjvjOaXwfNjH0AU656YrcGZEw@mail.gmail.com>
 <CAFPTHDZYonM+SXG19VVjgWduXQJSuDhcOUWq0NCiiuQubCSt6g@mail.gmail.com>
 <CAFPTHDYud1zr0VyizhyhEQXfHMgXVcHrPzE56WUKGCFNskQq2A@mail.gmail.com>
 <CAA4eK1JTau3fV7br6xwAV+LXXwM65RuGCuM2J3PQpCONtL1KXA@mail.gmail.com>
 <OS9PR01MB1691377CDB1468CDC9820BBEB9470A@OS9PR01MB16913.jpnprd01.prod.outlook.com>
 <TY4PR01MB1690715895CDE6FEFA13C2A2C947EA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
 <CAJpy0uA1txsV5RhjZjLBDrUjvxVyBDtMXzHr6=DzLHf7ybBrqg@mail.gmail.com>
 <TY4PR01MB1690739DE978BCBD12358478E947DA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
 <CAJpy0uAfu-VPqCknLLvJ+PUx_cyoR-b70xUNT6Pyv8N-odKizQ@mail.gmail.com>
 <CAJpy0uBeAdz6-3P26Eryeq3TyjA-GiKY3z0hFMxzZD=AYGqQ3Q@mail.gmail.com>
 <TY4PR01MB169072DEE7CC20E9B06F0164C947DA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
To: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Archived-At: 
 <https://www.postgresql.org/message-id/C860270A-26B8-46F3-9A01-4377B8BE9D5D%40gmail.com>
Precedence: bulk


> On Mar 5, 2026, at 19:34, Zhijie Hou (Fujitsu) =
<houzj.fnst@fujitsu.com> wrote:
>=20
> On Thursday, March 5, 2026 1:48 PM shveta malik =
<shveta.malik@gmail.com> wrote:
>>=20
>> On Thu, Mar 5, 2026 at 9:35=E2=80=AFAM shveta malik =
<shveta.malik@gmail.com>
>> wrote:
>>>=20
>>> On Thu, Mar 5, 2026 at 8:16=E2=80=AFAM Zhijie Hou (Fujitsu)
>>> <houzj.fnst@fujitsu.com> wrote:
>>>>=20
>>>>=20
>>>> Here is V10 patch set which addressed all comments.
>>>>=20
>>>=20
>>> Thank You. Please find a few comments on 001:
>>>=20
>>=20
>> A concern in 002:
>>=20
>> I realized that below might not be the correct logic to avoid =
overwriting
>> sequences at sub which are already at latest values.
>>=20
>> + /*
>> + * Skip synchronization if the local sequence value is already ahead =
of
>> + * the publisher's value.
>> ...
>> + */
>> + if (local_last_value > seqinfo->last_value) { ereport(WARNING,
>> + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
>> + errmsg("skipped synchronizing the sequence \"%s.%s\"",
>> +    seqinfo->nspname, seqinfo->seqname), errdetail("The local
>> + last_value %lld is ahead of the one on publisher",
>> +   (long long int) local_last_value));
>> +
>> + return COPYSEQ_NO_DRIFT;
>> + }
>>=20
>>=20
>> A sequence could be descending one too and thus we may wrongly end up
>> avoiding synchronization. We should first check if it is descending =
or ascending
>> (perhaps by checking if increment_by < 0 or >0), then decide to =
manage
>> conflict.
>=20
> Thanks for catching this, I changed it to check the increment before =
reporting warning.
>=20
> Best Regards,
> Hou zj

Hi,

I just started reviewing this patch and wanted to first discuss the =
design.

The current approach introduces a long-lived sync worker for any =
subscription that has at least one sequence. I noticed a previous email =
suggesting that this approach is =E2=80=9Cacceptable=E2=80=9D, but it =
still seems like a big runtime cost.

What I had in mind instead is whether we could extend the WAL decoding =
protocol to send RM_SEQ_ID over the logical replication stream, so that =
sequence synchronization becomes part of logical replication itself. =
That would make it essentially event-driven and close to zero cost at =
runtime, rather than relying on periodic polling.

There is also one case I haven=E2=80=99t seen discussed yet. Suppose the =
standby side inserts a tuple into a table that is under logical =
replication. This might not immediately cause a tuple-level replication =
conflict, but it could advance the sequence locally. In that case, the =
standby sequence could diverge from the primary sequence and remain out =
of sync indefinitely. How should that situation be handled?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/