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 1iRAlA-0007PN-Ad for pgsql-bugs@arkaria.postgresql.org; Sun, 03 Nov 2019 07:57:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iRAl6-0002qu-R1 for pgsql-bugs@arkaria.postgresql.org; Sun, 03 Nov 2019 07:57:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iRAl6-0002qn-60 for pgsql-bugs@lists.postgresql.org; Sun, 03 Nov 2019 07:57:12 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iRAky-0001hT-7M for pgsql-bugs@lists.postgresql.org; Sun, 03 Nov 2019 07:57:10 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id C427F21C25C6 for ; Sun, 3 Nov 2019 10:57:01 +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 81BC021C2584 for ; Sun, 3 Nov 2019 10:57:01 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1572767821; bh=XbMaJjVn7xDld+CfVAJdSkpD3VD/nqKO42HFVxieFKg=; h=From:Subject:To:Date; b=PKlt36ztNf71vNuQ7GjUQOpkrkhVinZdk5Rof5IeizTnqdjtIl7Nc08/8UBQsvWoH m1IoCO1eBuvEvXjI+45D9r89Fnv656k9dBWMeUhgXRi/2+8dagamc5bboXNzzT49uU R+t3KBP5rLCCKQ44WLtWnn+Lpnmrjn1yRFoOE/uM= From: Andrey Lepikhov Subject: Logical replication can be broken by domain constraint with NOT VALID option Organization: Postgres Professional To: pgsql-bugs@lists.postgresql.org Message-ID: <4bdc6250-03c6-0090-df77-580f998c4547@postgrespro.ru> Date: Sun, 3 Nov 2019 12:57:00 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------31FCBA9DD64FBEB36F7FFDD1" 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. --------------31FCBA9DD64FBEB36F7FFDD1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Hi, During patch development I ran into a small problem (see attachment, fail_replication.sh): 1. We have a table with logical replication to another node. 2. On the master and replica add such "NOT VALID" domain constraint on the table that some tuples violates the constraint. 3. UPDATE the table: set value of the tuple that violates constraint to correct value. 4. That's all! 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. Because we can't apply WAL record of the UPDATE command, logical replication will be stopped at all. As I understand, this problem can be reproduced in all postgres versions with logical replication feature. 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. -- Andrey Lepikhov Postgres Professional https://postgrespro.com The Russian Postgres Company --------------31FCBA9DD64FBEB36F7FFDD1 Content-Type: application/x-shellscript; name="fail_replication.sh" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="fail_replication.sh" CiMgUGF0aHMKUEdESVI9YHB3ZGAKSU5TVERJUj0kUEdESVIvdG1wX2luc3RhbGwKTT0kUEdE SVIvUEdEQVRBCk0xPSRQR0RJUi9QR0RBVEFfUkVQTApVPWB3aG9hbWlgCmV4cG9ydCBMRF9M SUJSQVJZX1BBVEg9JElOU1RESVIvbGliOiRMRF9MSUJSQVJZX1BBVEgKZXhwb3J0IFBBVEg9 JElOU1RESVIvYmluOiRQQVRICgojIENsZWFyIGRhdGEgb2YgcHJldmlvdXMgbGF1bmNoCnBr aWxsIC1VICRVIC05IC1lIHBvc3RncmVzCnJtIC1yZiAkTSB8fCB0cnVlCm1rZGlyICRNCnJt IC1yZiAkTTEgfHwgdHJ1ZQpta2RpciAkTTEKcm0gLXJmIGxvZ2ZpbGUubG9nIHx8IHRydWUK cm0gLXJmIGxvZ2ZpbGVfcmVwbC5sb2cgfHwgdHJ1ZQoKbWFrZQptYWtlIGluc3RhbGwKCmlu aXRkYiAtRCAkTQppbml0ZGIgLUQgJE0xCgojIEFkZGl0aW9uYWwgcHJlZmVyZW5jZXMKZWNo byAid2FsX2xldmVsID0gJ2xvZ2ljYWwnIiA+PiAkTS9wb3N0Z3Jlc3FsLmNvbmYKZWNobyAi d2FsX2xldmVsID0gJ2xvZ2ljYWwnIiA+PiAkTTEvcG9zdGdyZXNxbC5jb25mCgpwZ19jdGwg LXcgLUQgJE0gLWwgbG9nZmlsZS5sb2cgc3RhcnQKcGdfY3RsIC1vICItcCA1NDMzIiAtdyAt RCAkTTEgLWwgbG9nZmlsZV9yZXBsLmxvZyBzdGFydApjcmVhdGVkYiAkVQpjcmVhdGVkYiAt cCA1NDMzICRVCgppbml0X3NxbD0iQ1JFQVRFIERPTUFJTiB0aGluZ3MgQVMgSU5UOyBDUkVB VEUgVEFCTEUgdGhldGhpbmdzIChrZXkgdGhpbmdzIFBSSU1BUlkgS0VZKTsiCnBzcWwgLWMg IiRpbml0X3NxbCIKcHNxbCAtcCA1NDMzIC1jICIkaW5pdF9zcWwiCgpwc3FsIC1jICJDUkVB VEUgUFVCTElDQVRJT04gbXB1YiBGT1IgVEFCTEUgdGhldGhpbmdzIgpwc3FsIC1wIDU0MzMg LWMgIkNSRUFURSBTVUJTQ1JJUFRJT04gbXlzdWIgQ09OTkVDVElPTiAncG9ydD01NDMyIGRi bmFtZT0kVScgUFVCTElDQVRJT04gbXB1YjsiCgpwc3FsIC1jICJJTlNFUlQgSU5UTyB0aGV0 aGluZ3MgKGtleSkgVkFMVUVTICgxMSk7IgpzbGVlcCAxMCAjIFdhaXQgZm9yIHRoZSByZXBs aWNhdGlvbiBzeW5jIG9mIElOU0VSVCBjb21tYW5kCgpwc3FsIC1jICJBTFRFUiBET01BSU4g dGhpbmdzIEFERCBDT05TVFJBSU5UIG1lb3cgQ0hFQ0sgKFZBTFVFIDwgMTEpIE5PVCBWQUxJ RDsiCnBzcWwgLXAgNTQzMyAtYyAiQUxURVIgRE9NQUlOIHRoaW5ncyBBREQgQ09OU1RSQUlO VCBtZW93IENIRUNLIChWQUxVRSA8IDExKSBOT1QgVkFMSUQ7Igpwc3FsIC1jICJVUERBVEUg dGhldGhpbmdzIFNFVCBrZXkgPSBrZXkgLSAxOyIKc2xlZXAgMTAgIyBXYWl0IGZvciB0aGUg cmVwbGljYXRpb24gc3luYyBvZiBVUERBVEUgY29tbWFuZAoKcHNxbCAtcCA1NDMzIC1jICJT RUxFQ1QgKiBGUk9NIHRoZXRoaW5nczsiICMgd2UgY2FuJ3QgZm91bmQgbmV3IHZlcnNpb24g b2YgdHVwbGUKCiMgU2VlIHByb2JsZW1zIGF0IHRoZSBwb3N0Z3JlcyBsb2dzIG9mIG1hc3Rl ciBhbmQgc2xhdmUgbm9kZXMKCg== --------------31FCBA9DD64FBEB36F7FFDD1 Content-Type: text/x-patch; name="0001-Fix-the-problem-of-logical-replication-with-domain-N.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0001-Fix-the-problem-of-logical-replication-with-domain-N.pa"; filename*1="tch"