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.98.2) (envelope-from ) id 1xPLiJ-00000000bsS-0BB9 for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Nov 2026 15:18:48 +0000 Received: from localhost ([127.0.0.1]:48838 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPLiJ-00000000Nm0-1jfd for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Nov 2026 15:18:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:54310) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPLiJ-00000000Nlq-0agC for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 15:18:47 +0000 Received: from mail-wm1-x336.google.com ([2a00:1450:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xPLiG-00000000f5P-3Sqs for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 15:18:46 +0000 Received: by mail-wm1-x336.google.com with SMTP id 5b1f17b1804b1-4a215e64962so8735035e9.1 for ; Fri, 06 Nov 2026 07:18:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1793978324; x=1794583124; darn=lists.postgresql.org; h=in-reply-to:content-disposition:content-type:mime-version :references:message-id:subject:cc:to:from:date:from:to:cc:subject :date:message-id:reply-to:content-type; bh=XeMg5zq+A8GyRQcubg3UAdSJ+JnmKrJOKUZ97ARn8F0=; b=P//tp6GeE5rMvP4RcB1k3gFPXxvZc1o3P20vV/TxiNgS+OxiatfipXrwLJ9p8UYiBx no0zwxASOR85a8hZtC4FUVfGcONfP3ZnohVrsNnCYOtZSEjTHFL+4o3BdxdgoNntlEe1 WPXT1n40QYj9KzF1SXdTqXSQooZadtYILS2U0zsyQt/wCPtm2Yz2k3ppGJTn6JKrICmd Oihw1FZ3ylPJz0K6Kdz4lSubZEYClwR7QEcoN6cHvmeayIouNKLV/0mLmmcLtXFA59NK 8BluMPCl0f9nvUT2PdZcCNvd4/vjA3gWvEEwOv4YpcQk4A49XvmqD+UyxR85gOfjLIEO 0/RA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1793978324; x=1794583124; h=in-reply-to:content-disposition:content-type:mime-version :references:message-id:subject:cc:to:from:date:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=XeMg5zq+A8GyRQcubg3UAdSJ+JnmKrJOKUZ97ARn8F0=; b=So7zYjcPvYEKZWRZ7hicCig5Gc19o2TFsggY2R1XmY0K9WQL5qsX7avRHz9Jt0eAnI eB9QBE9JKd/4ZGAz+CBdCnzJpCVPR6m451pGHAvxbVe9EXcXcLlsayoF6hkzGL4Iq4mB FTM+ChgHjuw0WXMqxi56dT3ONFf8cB5968zwGhf5C/bJ6ebxuwZcbXlLuBKtRbMnyIel sh0bk+NEnCN+8R7W1j7ipWaiuGN00ktV35dveSZoG4hIvbZVfHVjzsq0iW08sTGhR6ZB ttHfFCCwqE5ECXjRlpCKq1NkjrQCKCcumbdbaV0HFHPdtju8WwVq2Z5iGDCYgSK5ZxTe G/zA== X-Gm-Message-State: APuU4Z8brvUrRybzsyzsQVHfVXZdF8NDzgTT8cRPmEROvBGLFiH7GldZ 3UcYiG3M5i7ktN5pdl7r6Yr+eQFdTkgSolkpSQOr5Dzp2Jm82RdZwD9NqQI5CQ== X-Gm-Gg: AYBFou3GEFgetzxrxvWZQQ7XIuvLnhQ9QvS4Mr3WbSFi6IjAnmxkfrKF/HYmPPIUEOm fxOthROPHzbMnpBqiGGKGqBGq9kO/2N/oqL/e5SXI2hrR5+uw6SkBIH2Ui1hQ5nFw3EZFzYHnov TvOca9G9X7fv/77PVg44vOREMSay+joEB/2A1ISGlxxEVzLwjS4UgBIT4yIG6/q1DDggfETjRfe Y9SxbP+KnpPbIAueRL5+cZ673MvYFuqhXztVGQqlxkVUGOWdNVoJv+Ht2CXxd0le5+eX106+8CD PcJUqMjUsaEOromH7yhch+ES9gP2TQe+YWBuN7spfVyv6LD5p4NtiLxsqOGVPWto3yIqzpqBbuJ zUfKbQLF1J75mgYsZjfZf9zMix8xLTfchPi0nVdvsT3GAHx5BVNb02mnDl8/woYnCmNhTuB93Bt 46KUoMf6QM9yAJMTm/pBJILQQIFvPZ1W5arwgJuFFRU4hO3okcgE1BUopi2urySCEaPAzEms5RN hdgd4kuOH5QL8I/URRW6SeKoflZdkDeJPwBEzGuk4NQXJInCyrvabdpcMRcCbsROLKtnz41bPE3 Ltp1A6WT6z5DE18Wgin9NTJHWbYKoSzRNdW9fJBRCtKP/c8SvLVy88q99omWFU0S3LsCCE787CT Y1LG5+nk1wcE= X-Received: by 2002:a05:600c:608a:b0:49f:e8bf:8f9b with SMTP id 5b1f17b1804b1-4b6dc6dec3emr49820225e9.4.1793978323589; Fri, 06 Nov 2026 07:18:43 -0800 (PST) Received: from bdtpg (ec2-15-237-197-144.eu-west-3.compute.amazonaws.com. [15.237.197.144]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-4b572caf48bsm159719175e9.11.2026.11.06.07.18.43 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 06 Nov 2026 07:18:43 -0800 (PST) Date: Fri, 6 Nov 2026 15:18:42 +0000 From: Bertrand Drouvot To: yk.verma2000@gmail.com Cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed Message-ID: References: <19733-5fcd39e85fa221a4@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <19733-5fcd39e85fa221a4@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On Wed, Sep 30, 2026 at 04:30:43AM +0000, PG Bug reporting form wrote: > A transaction updates a row, calls pg_logical_emit_message(true, ...) with > the row's new version, and commits. A client streaming the slot with > pg_recvlogical receives the message and then runs a SELECT on that row > from a separate ordinary session (READ COMMITTED, autocommit, same server). > Occasionally that SELECT returns the row version from before the update. > > So a transaction's changes can be invisible to a new snapshot taken after > the client has received that transaction's decoded output. Indeed, the commit WAL record can already be decoded while the transaction still appears as running in the procarray, as described by this comment in RecordTransactionCommit(): " * Note that at this stage we have marked clog, but still show as running * in the procarray and continue to hold locks. " The same "race" has already been discussed, like for example in [1]. > We hit this in production, where a service uses transactional logical > messages as an outbox. The consumer re-reads the row when it gets the > message and sometimes reads stale data. Our current workaround is to sleep > 100 ms before the read. I did not look at what it would take to close this race but want to say that relying on a hardcoded sleep as workaround looks fragile. Maybe a better workaround would be to also put the top level XID in the message (can get it with pg_current_xact_id()) and then have the consumer poll: SELECT pg_visible_in_snapshot(, pg_current_snapshot()); until it returns true (or poll pg_xact_status() while it returns "in progress"). Each poll and the subsequent row read should be separate READ COMMITTED statements. [1]: https://postgr.es/m/CAL9smLBPrRLj7uwUrxtVT7wdkzJd%2Bj%3DCB%2BSRepOpS0hgtP8dog%40mail.gmail.com Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com