MIME-Version: 1.0
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>
In-Reply-To: 
 <CAJpy0uAfu-VPqCknLLvJ+PUx_cyoR-b70xUNT6Pyv8N-odKizQ@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Thu, 5 Mar 2026 11:18:20 +0530
Message-ID: 
 <CAJpy0uBeAdz6-3P26Eryeq3TyjA-GiKY3z0hFMxzZD=AYGqQ3Q@mail.gmail.com>
Subject: Re: [PATCH] Support automatic sequence replication
To: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Cc: 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>,
 shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJpy0uBeAdz6-3P26Eryeq3TyjA-GiKY3z0hFMxzZD%3DAYGqQ3Q%40mail.gmail.com>
Precedence: bulk

On Thu, Mar 5, 2026 at 9:35=E2=80=AFAM shveta malik <shveta.malik@gmail.com=
> wrote:
>
> On Thu, Mar 5, 2026 at 8:16=E2=80=AFAM Zhijie Hou (Fujitsu)
> <houzj.fnst@fujitsu.com> wrote:
> >
> >
> > Here is V10 patch set which addressed all comments.
> >
>
> Thank You. Please find a few comments on 001:
>

A concern in 002:

I realized that below might not be the correct logic to avoid
overwriting sequences at sub which are already at latest values.

+ /*
+ * 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;
+ }


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.

Example:
postgres=3D# CREATE SEQUENCE desc_seq START WITH 1000 INCREMENT BY -1
MINVALUE 1 MAXVALUE 1000;
CREATE SEQUENCE
postgres=3D# select nextval('desc_seq');
nextval
---------
    1000

postgres=3D# select nextval('desc_seq');
nextval
---------
     999

Doc also mentions descending sequences. See [1] (search for descending).

[1]: https://www.postgresql.org/docs/18/sql-createsequence.html

thanks
Shveta