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 1wi5De-0000PU-2N for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Jul 2026 07:00:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wi5Dd-00GQJc-0P for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Jul 2026 07:00:17 +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 1wi5Dc-00GQJU-2n for pgsql-hackers@lists.postgresql.org; Fri, 10 Jul 2026 07:00:17 +0000 Received: from mail-yx1-xb129.google.com ([2607:f8b0:4864:20::b129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wi5Db-00000000R8y-2dAR for pgsql-hackers@postgresql.org; Fri, 10 Jul 2026 07:00:16 +0000 Received: by mail-yx1-xb129.google.com with SMTP id 956f58d0204a3-664d35b4777so813513d50.3 for ; Fri, 10 Jul 2026 00:00:15 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1783666815; cv=none; d=google.com; s=arc-20260327; b=TBKC8G7KweTk6tJRMyQZQ7DAsQCynQ2TySgrrjeZD0Br/OYHkO6Y/E9v0iUmTiKwEg 9iW7LGlxAcRC8PY6hhRzGtHRPZcFDDDyPxfHP/NSj+WBtljaAPuBIh5Ky4RRujKS20rW oaLvDiOjJTX1qUf0m9JlRiQ1aM5r/AR2eAmFX/8HNJPkWGcTljXdSzfgPLl6oFtmm2Tm FvSA1/Dw8JVSbDx4LGFhf0SmH2ShOeM6y/E2mqCm3hyVRlhRs1TwF64igK9bW5QAZWMF +0/P6ooJttlCKfrRUZ20ShFguHmzBZe+K3Fulv0XHezO6BXpHQtFCcM36eWsRc0zxTKc 0v+g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20260327; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=cxmq6POLZcSmVlUjSqbEvcKbrqdc7xshKJXpxFfzXKU=; fh=+7PTxawvZzAF7eyJyBk0fqfuYzIDe497RiH7iUJZAEM=; b=DsWRBBjVRymephadVlq/sF/14Wlh3JcfQogoyY03KQ7AhhFbTrHhE44HeadaLrGEh4 GM6mCe/TeUFcH3RxA4eDqDEzPV/XUR3ebgos3p6TpoCt0wpQ8sch7XJOdHwpD6Byn9Ew 46+PiN/W9AsSgutCIIk57Ulp2E9SyXxaQMN5B+b1GdC/9lfVhATgyO8c0+6qLQal3tUW GJsG6XnyI6+wqqSoe8ddXF2fBRXYdk+hC6jzMB2Zt/WRpnO6QX+4xBP3Q1LMfSO/yePq +1BwI8mKjMuPjLKhd5Ca/tDzWEAJZ4SC22oFO9FKqAmFAle1WXLj/DMbcuat7RSoRrFj jTEQ==; darn=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=20251104; t=1783666815; x=1784271615; darn=postgresql.org; h=content-type:cc:to:subject:message-id:date:from:in-reply-to :references:mime-version:from:to:cc:subject:date:message-id:reply-to :content-type; bh=cxmq6POLZcSmVlUjSqbEvcKbrqdc7xshKJXpxFfzXKU=; b=axSh1Q1vT+CNrczGU+xHVOgFtFxkbdEDPk4OR7O6mC88WgPpssqPHNydFyfCNXqrTE dUFZqDw510eRVObZKy4RwVDWkEQnGaZnBkhzAqQnjW52Onafi5lFvaRqw+orERPJ05+t kQUKJPugON/ZHYOw8kyoH1ytU+wDoA31j0+f8HhvFJekOII5Z26PGRjQZrmM4XLVqr+5 tL0DSMu1iDy28TN7u/gAosuIm7sFJjJQ83TPoraiZCgsfdiKzs1yRkQCzBYDMe2WmVmr d2hFaf7Ijixgt1reBOU5XOQZx1vMiPJZ4NLDco7wyOY5UEJCmmkHQ0zZp4O6iDWPph2V Hxiw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1783666815; x=1784271615; h=content-type: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:content-type; bh=cxmq6POLZcSmVlUjSqbEvcKbrqdc7xshKJXpxFfzXKU=; b=GQHdiKAEC7flJDTXeWbjmk2pix2VPlqoXMedphk37lnBSliuiU4AqQX3lT1SDWt5ZW AJHELAaGRvVza+8SJvYLwGd4pSxv2IQcCbd9/mBUiFVJWug0FFEHcfc1Aa6KHGKgb59u /x0zmDciQuBjsiwQQ4NYa0Ur566dCe2FsUVlphbhbEn/TEry7LwbsIEzrMcUNsw/QXXd bHhjOLvxeELYS1zqbs54b4lUm4qq7NtgD4r3yQr6lelqyspCP3m0/lOTKdTZc4WsBNwK VhdhdTNRAgnWt4PrhE1K43nruAWrBG6BKXOLwKKx78dv3lfqeNwY/5g1JMclP6WwDUhj vvJA== X-Forwarded-Encrypted: i=1; AHgh+Rrby6nIAwAsJePEdJwRKgqWDIaL63FzPL5ijUN7EA7zowU2bkjlqNzdHxEZpeB6VdpL/PN7woXh6gIc5pcs@postgresql.org X-Gm-Message-State: AOJu0YxO/8bRxMkV7RARFsz8RDseIkMWE/hj4CTEpBQDne1AFwoGRLqA UzybtQiSZwt9W/Cf8BvfANQp7ssPWlymZ0JdnLcUIRibuZKZPNFD3xRk7GA/7JMfdIhSX+pFCTk HTIr3Q7DbmiMZkkrNjwXhBLGlDa74gp2UMIwpvoo= X-Gm-Gg: AfdE7ck1Bh6LYXPwzR0IMsdfw+xjbdxoW8lhybN616ijB3eAzYUnspOuByccVquJ74g +9vLpCwVKV0S4UoaIjoH/otJWvfw8wDPsMoA5x6GdKWJloUnpPB2ukFulkoTqxPsqnDEVBu+QaE IyldXpxVLMMIckqXEzzM3+sSu6xCLB6KKq4bKxJpsTsKmVYhIbVs/qiBZFa5UGaAHBJosBL1EUE i+V0C8Xy+9Xf5Fq0T/EyJM+EILpyU7z2f5hiOOm3iqx+ofhEH12jLKDhUphhVXh8LR4JQOi2T8r Q7zCV52OqYaBLMtBzIpsDJ/sgQFVn3Y= X-Received: by 2002:a05:690e:12c5:b0:667:bef1:c2fb with SMTP id 956f58d0204a3-667bef1cce6mr2951607d50.96.1783666814983; Fri, 10 Jul 2026 00:00:14 -0700 (PDT) MIME-Version: 1.0 References: <20260710045217.f0.noahmisch@microsoft.com> In-Reply-To: <20260710045217.f0.noahmisch@microsoft.com> From: vignesh C Date: Fri, 10 Jul 2026 12:30:02 +0530 X-Gm-Features: AVVi8CcUVv0aWQmoHJjF_pevqZSNbr9WMc-2UXbn3M7icasR2G44oFqBpTM81EA Message-ID: Subject: Re: sequencesync worker race with REFRESH SEQUENCES To: Noah Misch Cc: amit.kapila16@gmail.com, pgsql-hackers@postgresql.org Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 10 Jul 2026 at 10:22, Noah Misch wrote: > > A Fable 5 review of logical replication of sequences found a way to get > subscribed sequences into READY state despite the subscriber side having data > older than the last REFRESH SEQUENCES. I'm attaching the test case it wrote. > I reviewed the test, and I think it identifies a genuine defect. Thanks for reporting this. The test case reproduces this issue. This issue is not limited to lock contention. It can also occur if the sequence synchronization worker is simply slow. For example, the worker may fetch the sequence value from the publisher, after which the publisher sequence advances and ALTER SUBSCRIPTION ... REFRESH SEQUENCES is executed. When the worker eventually updates the subscriber, it uses the stale value that it fetched earlier, overwriting the sequence with an out-of-date value. How about raising a warning for the second REFRESH SEQUENCES indicating that the sequence is already being synchronized and skipping it? The warning could also include a hint to rerun ALTER SUBSCRIPTION ... REFRESH SEQUENCES after the current synchronization completes. This approach avoids making ALTER SUBSCRIPTION ... REFRESH SEQUENCES wait for the sequence synchronization worker, which could itself be blocked waiting for a lock held by a long-running transaction on the sequence. Regards, Vignesh