Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqmMa-000BPb-03 for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Feb 2026 06:09:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vqmMZ-00CllR-04 for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Feb 2026 06:09:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqmMY-00CllI-1w for pgsql-hackers@lists.postgresql.org; Fri, 13 Feb 2026 06:09:11 +0000 Received: from mail-dl1-x122e.google.com ([2607:f8b0:4864:20::122e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vqmMW-00000000Oyi-2yg2 for pgsql-hackers@lists.postgresql.org; Fri, 13 Feb 2026 06:09:10 +0000 Received: by mail-dl1-x122e.google.com with SMTP id a92af1059eb24-1271195d2a7so1324830c88.0 for ; Thu, 12 Feb 2026 22:09:09 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770962949; cv=none; d=google.com; s=arc-20240605; b=dH28+LG10hHG1NT1ZP+TpqKhL7UgXw6XnY3d0U+RAzFH2v0UYsWOHKTwFN7mA43R7u Wq+C0tYwIZDGExu6/TJ1jMNibPoV7/jzIrC0qDd1xA/9FUwHE7+Ka7I67q5pNxirSnF+ fb0htIanHl+51sGokTTGdNXD/tX9MIgbPIbh8ri7hvjobboJ/QkQURr0sFp0xkDNqLx/ Lc/B69X1/QuPEeXuGnYaoJqPfBc2t/7cvHRAA2H3M28lPupdFoxnWCdBu1CrQZsamZ5O nZIfLYLfW7b01DtkgqWKven/g5bTEKyUynfmgyr51kzSKmp6BbphLUQ4ix7ESJRmVQw2 N6ng== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=zQwF1RKb82vhLimSdlyWKC6iLrc7qi9H/ktRWm6V2UU=; fh=pOFqMSv0ZgiWxaYGHfnGU55XEifwPEhvLSMiqwTzR9E=; b=GvsmNtjJtveHz5q3AQHM0qa6QEc4TfS9LYKEX2EkTc3E1V2EdriWqPGKMCcS70+4aQ psnevSeWR4O3I+S+DOY7HiUYYx7Vke9/OPKM9MDA2OyxsV1cTfvlLANTVbc8zuETNzwA M7qL2C2ADsNYWq/jvgXT11Uy7QYI1RBzFpZ/FakflD2llaTpsB5O9H/Zu8dEG+4uf1Kv klaIZ2v+4CNUH8BoFCBB49T2ZnBicDefVc2AOlolbL6+fxuKK0Y20/T4K8Aqfhmw3SLR l6aYVGSHqcbOJXCd8Hb3Cdq7eGxI7jpZ8/JskAKR8ku1PZqTZohTpuhjr8k3tqOWq7Sv GTFg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770962949; x=1771567749; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zQwF1RKb82vhLimSdlyWKC6iLrc7qi9H/ktRWm6V2UU=; b=IYda3a17jine3Qa5qqzmnBU+sF51RqJX2zfbgjoC9FHhcfdTBAA4aMBmrQOB//rIOV Wgc/Ph0tEQ6S9ff6sVwf841A8EQFEv4S5bkdtAXZgX21wvAByyyTXaVgI4ekVeLgkT1o sUKudTunV39dkYmJkNuuRyJhbdlR2tTK8RXMGMcOCdqFxqTgpIek9CPHAQgxrBm9Z/yD b9YSf1eRKq9/2NxMjmByb/i2HVpAIpX6AtRfS/foPLP2v4Qm+fYgM7qGV+QP8VatN3Mk FrrlMtGo9Uy9LV7wS/8B/RsCbbpWWNeNBByz1HI77+I12Kh78U1UO9NznhHrXtr5fdNW vAUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770962949; x=1771567749; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=zQwF1RKb82vhLimSdlyWKC6iLrc7qi9H/ktRWm6V2UU=; b=Hku6deVm+/U6cAaDQBvwZ20eAFITDHmx/9OwG8EgGbnTBi3y8co/piuLyLreKSxKYR LUiftQvKSnOyUYn775JFVPFBYn+Vq3lUv7mVAUGLk1SJqnQjVEEDAwciu7K7+8fsmNQx sWtZZ9+wPJoY+Xr1b6b3RiVgzt2WLRr6LLZAkQnRF0DXf4JFOs/pbN3I6voJ4IfDpHQw OATXBMqEpeblEaRgTE8nE+jG678uab+3BCgIxoZi7/EApYBgKhhqF08n2aRdAjCs0IFX v5XX2K8fROT5dxUdyAyJmZtHrxUtZ38/qNPHzlKPtuAxKxz1FUaFuDQZAYChps5Ku0cP HMIA== X-Gm-Message-State: AOJu0YxpX2Bx6CWhmHuMnXOSgcGI7ciPhJ8jryfPw/CcsYrQWaE7IIcT SjPUIyFhz81fJMO3hDppOKUxVWFtV3eXCywqPCaf9qI/7uMTYd3DpT+Z64Agksl35/xTGw9nPwE G4Kd80l3LIrV5/gsxmHYxxuDtJej4ibw= X-Gm-Gg: AZuq6aJ0ELYSJH6VLgm+sgfzNUP+/B0xmoOKLV3tlCKFunucFSjPQY2Op+UHYjdxxuA l/zctweRhFNz5CJQEK1YQ09yhSvKCuKCMa/jmfNh42kExyfDuMCNlLEdkUVB15IDct9Ef5+c7ml uv2hCgk1yeaMaxaglAd7nCbpbRVYw82rNE7puAWGKu8xkSmeJDD/bo/YpU6RnV1927XrniNmsT2 wviZQwPuA6TDBQC/w1+eX/+ohg1jiJaEyvFP/7E48grcfYp+rX5rQMrVp+Kx7/Sp/rDENbQANwR AgHA8I6qcY25a6ufekxQwzAI9KLk+1ViFlKZ X-Received: by 2002:a05:7022:227:b0:11a:4ffb:9825 with SMTP id a92af1059eb24-1273ade4561mr259414c88.6.1770962948552; Thu, 12 Feb 2026 22:09:08 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ashutosh Sharma Date: Fri, 13 Feb 2026 11:38:55 +0530 X-Gm-Features: AZwV_QgqTAXmCcXAp1N7-Nkexjqh43zxdf_LHBmhbkIDeOC0VRBlw-GM7pBQNyw Message-ID: Subject: Re: [PATCH] Support automatic sequence replication To: Ajin Cherian Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000757d10064aae713d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000757d10064aae713d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, On Tue, Feb 3, 2026 at 9:18=E2=80=AFAM Ajin Cherian wro= te: > > Hello hackers, > > I'd like to propose an improvement to the sequence replication feature > that was committed in [1]. > > The current implementation synchronizes sequences during initial > subscription setup, but the sequence sync worker exits after this > initial sync. This means that as sequences advance on the publisher, > they drift from the subscriber values over time. Users must manually > run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to resynchronize, which > requires monitoring and intervention. > > Proposed Enhancement: > > This patch changes the sequence sync worker to run continuously > throughout the subscription lifetime, automatically detecting and > correcting sequence drift. The key changes are: > > 1. The sequence sync worker remains running instead of exiting after > initial sync, periodically checking for and synchronizing drifted > sequences. > > 2. The worker uses an exponential backoff strategy - starting at 2 > seconds, doubling up to a maximum of 30 seconds when sequences are in > sync, and resetting to the minimum interval when drift is detected. > > 3. Since synchronization is now automatic, ALTER SUBSCRIPTION ... > REFRESH SEQUENCES is no longer needed and has been removed. > > The patch modifies documentation to reflect the new behavior, removes > the REFRESH SEQUENCES command from the grammar and subscription > commands, and implements the continuous monitoring loop in > sequencesync.c. Tap tests have been updated to verify automatic > synchronization rather than manual refresh. > > The attached v2 patch is attached and ready for review. > > Thoughts and feedback are welcome! > > [1] - https://github.com/postgres/postgres/commit/5509055d6956745532e65ab218e15b9= 9d87d66ce > Is this expected behavior? 1) *Publisher:* *create sequence t1_seq;create table t1 (id int default nextval('t1_seq') primary key, a int);create publication t1_pub for table t1;create publication t1_seq_pub for all sequences;* 2) *Subscriber:* *create sequence t1_seq;create table t1 (id int default nextval('t1_seq') primary key, a int);create subscription t1_sub connection 'host=3D127.0.0.1 port=3D37500 dbname=3Dtest user=3D$USER' publication t1_pub with (create_sl= ot =3D false, slot_name =3D 't1_sub');create subscription t1_seq_sub connection 'host=3D127.0.0.1 port=3D37500 dbname=3Dtest user=3D$USER' publication t1_s= eq_pub with (create_slot =3D false, slot_name =3D 't1_seq_sub');select * from pg_subscription_rel;select * from pg_sequences;* 3) *Publisher:* *insert into t1(a) values(10);select * from pg_sequences;* 4) *Subscriber:* *select * from pg_sequences;* -- in sync with publisher. *insert into t1(a) values(20);* *select * from pg_sequences;* -- the sequence gets deviated from publisher. After a few minutes, re-running the above shows that the sequence value is reset to match the publisher. However, any new insert on the subscriber fails: *insert into t1(a) values(30);* *ERROR: 23505: duplicate key value violates unique constraint "t1_pkey"DETAIL: Key (id)=3D(2) already exists.SCHEMA NAME: publicTABLE NAME: t1CONSTRAINT NAME: t1_pkey* -- Automatic sequence replication resets the last_value on the subscriber to match the publisher, which leads to duplicate key conflicts and prevents further inserts on the subscriber. -- With Regards, Ashutosh Sharma. --000000000000757d10064aae713d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

On Tue, Feb 3, 2026 at 9:18=E2=80=AFAM Ajin Che= rian <itsajin@gmail.com> wro= te:
>
> Hello hackers,
>
> I'd like to propose = an improvement to the sequence replication feature
> that was committ= ed in [1].
>
> The current implementation synchronizes sequence= s during initial
> subscription setup, but the sequence sync worker e= xits after this
> initial sync. This means that as sequences advance = on the publisher,
> they drift from the subscriber values over time. = Users must manually
> run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to= resynchronize, which
> requires monitoring and intervention.
>=
> Proposed Enhancement:
>
> This patch changes the seque= nce sync worker to run continuously
> throughout the subscription lif= etime, automatically detecting and
> correcting sequence drift. The k= ey changes are:
>
> 1. The sequence sync worker remains running= instead of exiting after
> initial sync, periodically checking for a= nd synchronizing drifted
> sequences.
>
> 2. The worker u= ses an exponential backoff strategy - starting at 2
> seconds, doubli= ng up to a maximum of 30 seconds when sequences are in
> sync, and re= setting to the minimum interval when drift is detected.
>
> 3. = Since synchronization is now automatic, ALTER SUBSCRIPTION ...
> REFR= ESH SEQUENCES is no longer needed and has been removed.
>
> The= patch modifies documentation to reflect the new behavior, removes
> = the REFRESH SEQUENCES command from the grammar and subscription
> com= mands, and implements the continuous monitoring loop in
> sequencesyn= c.c. Tap tests have been updated to verify automatic
> synchronizatio= n rather than manual refresh.
>
> The attached v2 patch is atta= ched and ready for review.
>
> Thoughts and feedback are welcom= e!
>
> [1] - https://github.com/postgres/= postgres/commit/5509055d6956745532e65ab218e15b99d87d66ce
>
Is this expected behavior?

1) Publisher:

create se= quence t1_seq;
create table t1 (id int default nextval('t1_seq')= primary key, a int);

create publication t1_pub for table t1;
cre= ate publication t1_seq_pub for all sequences;

2) Subscriber:<= /b>

create sequence t1_seq;
create table t1 (id int default ne= xtval('t1_seq') primary key, a int);

create subscription t1_= sub connection 'host=3D127.0.0.1 port=3D37500 dbname=3Dtest user=3D$USE= R' publication t1_pub with (create_slot =3D false, slot_name =3D 't= 1_sub');
create subscription t1_seq_sub connection 'host=3D127.0= .0.1 port=3D37500 dbname=3Dtest user=3D$USER' publication t1_seq_pub wi= th (create_slot =3D false, slot_name =3D 't1_seq_sub');

sele= ct * from pg_subscription_rel;
select * from pg_sequences;

3)= Publisher:

insert into t1(a) values(10);
select * from= pg_sequences;

4) Subscriber:

select * from pg_= sequences; -- in sync with publisher.
insert into t1(a) values(20= );
select * from pg_sequences; -- the sequence gets deviated = from publisher.

After a few minutes, re-running the above shows that= the sequence value is reset to match the publisher. However, any new inser= t on the subscriber fails:

insert into t1(a) values(30);
<= b>ERROR: =C2=A023505: duplicate key value violates unique constraint "= t1_pkey"
DETAIL: =C2=A0Key (id)=3D(2) already exists.
SCHEMA NAM= E: =C2=A0public
TABLE NAME: =C2=A0t1
CONSTRAINT NAME: =C2=A0t1_pkey

--

Automatic sequence replication resets the last_value on= the subscriber to match the publisher, which leads to duplicate key confli= cts and prevents further inserts on the subscriber.

--
With Rega= rds,
Ashutosh Sharma.
--000000000000757d10064aae713d--