Re: [PATCH] Support automatic sequence replication - Mailing list pgsql-hackers

> On Mar 5, 2026, at 19:34, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> wrote:
>
> On Thursday, March 5, 2026 1:48 PM shveta malik <shveta.malik@gmail.com> wrote:
>>
>> 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.
>
> Thanks for catching this, I changed it to check the increment before reporting warning.
>
> 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
aprevious email suggesting that this approach is “acceptable”, 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
replicationstream, so that sequence synchronization becomes part of logical replication itself. That would make it
essentiallyevent-driven and close to zero cost at runtime, rather than relying on periodic polling. 

There is also one case I haven’t seen discussed yet. Suppose the standby side inserts a tuple into a table that is
underlogical replication. This might not immediately cause a tuple-level replication conflict, but it could advance the
sequencelocally. 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/