Re: [PATCH] Support automatic sequence replication

On Thu, Mar 5, 2026 at 9:35 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Mar 5, 2026 at 8:16 AM 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=# CREATE SEQUENCE desc_seq START WITH 1000 INCREMENT BY -1
MINVALUE 1 MAXVALUE 1000;
CREATE SEQUENCE
postgres=# select nextval('desc_seq');
nextval
---------
    1000

postgres=# 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