Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iRSB5-0004Cq-Ta for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Nov 2019 02:33:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iRSB4-0008Lu-6k for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Nov 2019 02:33:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iRSB3-0008Ln-QE for pgsql-bugs@lists.postgresql.org; Mon, 04 Nov 2019 02:33:10 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iRSAw-00078n-Ky for pgsql-bugs@lists.postgresql.org; Mon, 04 Nov 2019 02:33:09 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id D159721C24BC; Mon, 4 Nov 2019 05:33:00 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.0.108] (pool-77-222-104-35.is74.ru [77.222.104.35]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 7157321C23F3; Mon, 4 Nov 2019 05:33:00 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1572834780; bh=Mo7SRxxiPOA/MS84lUhWQXO8ozSNJ1skT1cr2jiAVMU=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=dheLm58/PY/B/Sj18gV0cnHBZtjvNuQLTpDHu0l+a87tGbrIKJInts/9tRu4wpFxR /b60W97L/QMdNddnMzda3FfUO/ZpqJpJnpWc/7Uoys3wwqWWieQW0ClB5rsRrpx/h9 ERY0/cQ/oV3wuJZy1lhM5moGx8jmH4CgD0l4zbqQ= Subject: Re: Logical replication can be broken by domain constraint with NOT VALID option To: Tom Lane Cc: pgsql-bugs@lists.postgresql.org References: <4bdc6250-03c6-0090-df77-580f998c4547@postgrespro.ru> <29155.1572795733@sss.pgh.pa.us> From: Andrey Lepikhov Organization: Postgres Professional Message-ID: <48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru> Date: Mon, 4 Nov 2019 07:32:58 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: <29155.1572795733@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------71BA81217F082BC8CC7235CF" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------71BA81217F082BC8CC7235CF Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 03/11/2019 20:42, Tom Lane wrote: > Andrey Lepikhov writes: >> The reason for this problem is that on UPDATE walsender sends old tuple >> value (that violates the constraint) with new version (satisfied the >> constraint). >> Replication worker at replica node restores slot from transfer >> representation. During this process domain checking constraint and >> returns an ERROR. > > I'm not sure this is something we should attempt to fix. There are > an infinite number of ways you can break logical replication by > presenting it with inconsistent data, and that's really what you've > done here. This problem reproduced by standard way from the documentation. I assume this inconsistency option is allowed by SQL standard because it has a practical usage. > >> This problem can be solved by many ways and approaches. I wrote the >> patch to solve this problem (see in attachment) by the shortest way. > > That patch is certainly utterly unacceptable. It'd allow the > receipient to accept data that violates the domain constraint. If this is the only reason, I propose a new version of the patch (see in attachment). It is satisfy the "Paranoid safety" rule. > > The situation you're describing would probably best be handled by > not adding the constraint on the replica side until all the > bad data has been corrected (and replicated). On any PostgreSQL-based multimaster system, this will be a problem. -- regards, Andrey Lepikhov Postgres Professional https://postgrespro.com The Russian Postgres Company --------------71BA81217F082BC8CC7235CF Content-Type: text/x-patch; name="v2-0001-Fix-the-problem-of-logical-replication-with-domain-N.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="v2-0001-Fix-the-problem-of-logical-replication-with-domain-N"; filename*1=".patch"