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 1izUQg-00036t-Rc for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 23:49:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1izUQf-0003FQ-Jk for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 23:49:57 +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 1izUQf-0003FJ-0B for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 23:49:57 +0000 Received: from mail.dalibo.com ([212.85.157.172]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1izUQb-0004Ub-Jd for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 23:49:56 +0000 Received: from firost (unknown [88.190.0.6]) by mail.dalibo.com (Postfix) with ESMTPSA id 9E1791F8BE for ; Thu, 6 Feb 2020 00:49:50 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1580946590; bh=IvGA9zk9h6RAfhfq50yiiuT84sFwh3At4yOGe8VcBbQ=; h=Date:From:To:Subject:From; b=WBr2ywoR1PywoqSnETZOsvmEOL1ruZE+5y1QHDs0tDG4WEppLmHApicP1MxbV8wNN SLkRJFnRuoWPRwshDCqhEPcHjZ5Q1HXj3TisC5id3sZxOIlY9vMg8YMBqu4DlZIxwA VRnoFXzhkz5EEqulAnqv3GgWKWWegmpcTky7n+O0= Date: Thu, 6 Feb 2020 00:49:48 +0100 From: Jehan-Guillaume de Rorthais To: Subject: Another FK violation when referencing a multi-level partitioned table Message-ID: <20200206004948.238352db@firost> Organization: Dalibo MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="MP_/Msd94S6s2wOg9g8UL86jLAL" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --MP_/Msd94S6s2wOg9g8UL86jLAL Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello, When working on the patch to fix another FK violation [1], I found that FK constraints were not properly cloned to partition not directly hooked to the root table. CloneFkReferenced takes care to avoid inherited constraints to clone top-level constraints only: /* * Search for any constraints where this partition is in the referenced * side. However, we must ignore any constraint whose parent constraint * is also going to be cloned, to avoid duplicates. [...] */ But it seems the top-level constraints are actually never cloned neither. Surprisingly, the comment explains how this should be done in two steps, but the code corrupted the first step by skipping inherited constraints and lacks the second step: * [...]to avoid duplicates. So do it in two * steps: first construct the list of constraints to clone, then go over * that list cloning those whose parents are not in the list. (We must * not rely on the parent being seen first, since the catalog scan could * return children first.) */ Please, find in attachment a proposal patch to fix this FK violation. Regards, [1] https://www.postgresql.org/message-id/20200204183906.115f693e%40firost --MP_/Msd94S6s2wOg9g8UL86jLAL Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=0001-v1-Fix-FK-violation-when-referencing-a-multi-level-part.patch