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.94.2) (envelope-from ) id 1qQZh3-000KhU-3G for pgsql-hackers@arkaria.postgresql.org; Mon, 31 Jul 2023 20:40:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qQZg1-001knW-Tk for pgsql-hackers@arkaria.postgresql.org; Mon, 31 Jul 2023 20:39:39 +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.94.2) (envelope-from ) id 1qQZg0-001knN-9J for pgsql-hackers@lists.postgresql.org; Mon, 31 Jul 2023 20:39:39 +0000 Received: from wout1-smtp.messagingengine.com ([64.147.123.24]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qQZfw-0003RM-2E for pgsql-hackers@postgresql.org; Mon, 31 Jul 2023 20:39:36 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id 8706C3200981; Mon, 31 Jul 2023 16:39:29 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Mon, 31 Jul 2023 16:39:29 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1690835969; x=1690922369; bh=LGEhdoif/rSxgaxmEG8Jio8X2vPIwTyaPCa PAfnfZyw=; b=IRRtWHAdVV/8/MDLJrTfngOmLim26SlZuIhQhdPGoK/JeLxAn+f vkC0HxEfLl4snWeDZKbaIL48xztZLZiNOM/cpZl0Jd+ciZ5ODKVIlLRhZx7teSxp SHJHVar55SfweQBn9uOIi99lLZa8Q4DutKXOk/lDeISctNTru01na+Rs5mgFZOcc /x8mAAw0VRBjr2HApa+cUgS/fYj13//GBbTUp/NsP90IOn41D1+guqCz4lO5o7R/ QIJe1/WEZpcLGs0iFIilCOAxnNij7c+W99c2gptNdL/xP54LJ5ovx+Fut6CInwft 5cicl5aOgHmK7dSpG8k5AGaxcjA7MEaPwgg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1690835969; x=1690922369; bh=LGEhdoif/rSxgaxmEG8Jio8X2vPIwTyaPCa PAfnfZyw=; b=Wum91xEQwOxaAFmvbPTBRR1iTn5pgJTC1afsv22gAaGbkvBQgfp 2DCbUPO/ofVTGv1bJgnfDrEq1pP+W7VaoADkqi4/uV34OROWVVSMevDUN9WNcC4p PE/7cd03/vpCNpxLX1FVGbWVjcxcCEk9zvkw0F84XzuGmh2OW12wHrNo2Ir5X5gQ zUN74qtpZdrcwzimFT0kpYrT0DB7/pyIZrPMBOekpvADSvRULgSjceFy4Dcu98C+ qoJdPIJ4j+YS6MnJUWHypf4WwcmEOM/NunZh8Vy+yTzIPoUhmat/I99ryiU4bvCa +T17LBmlMMouN7UnIKQzqGbmQEbe1xduBAA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrjeeggdduvdejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkfhggtggugfgjsehtkeertddttddunecuhfhrohhmpeetnhgu rhgvshcuhfhrvghunhguuceorghnughrvghssegrnhgrrhgriigvlhdruggvqeenucggtf frrghtthgvrhhnpedutdeigeeljeehueejieevgeeulefhkeelueffudekudeludffheff gfetieevhfenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhroh hmpegrnhgurhgvshesrghnrghrrgiivghlrdguvg X-ME-Proxy: Feedback-ID: id4a34324:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 31 Jul 2023 16:39:28 -0400 (EDT) Date: Mon, 31 Jul 2023 13:39:27 -0700 From: Andres Freund To: =?utf-8?B?Sm9zw6k=?= Neves Cc: Amit Kapila , "pgsql-hackers@postgresql.org" Subject: Re: CDC/ETL system on top of logical replication with pgoutput, custom client Message-ID: <20230731203927.6nurjhw5ltisfhc2@awork3.anarazel.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On 2023-07-31 14:16:22 +0000, José Neves wrote: > Hi Amit, thanks for the reply. > > In our worker (custom pg replication client), we care only about INSERT, > UPDATE, and DELETE operations, which - sure - may be part of the issue. That seems likely. Postgres streams out changes in commit order, not in order of the changes having been made (that'd not work due to rollbacks etc). If you just disregard transactions entirely, you'll get something bogus after retries. You don't need to store the details for each commit in the target system, just up to which LSN you have processed *commit records*. E.g. if you have received and safely stored up to commit 0/1000, you need to remember that. Are you using the 'streaming' mode / option to pgoutput? > 1. We have no way to match LSN operations with the respective commit, as > they have unordered offsets. Not sure what you mean with "unordered offsets"? > Assuming that all of them were received in order, we would commit all data with the commit message LSN4-4000 as other events would match the transaction start and end LSN interval of it. Logical decoding sends out changes in a deterministic order and you won't see out of order data when using TCP (the entire connection can obviously fail though). Andres