From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Another FK violation when referencing a multi-level partitioned table
Date: Thu, 6 Feb 2020 00:49:48 +0100
Message-ID: <20200206004948.238352db@firost> (raw)
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%40firostAttachments:
0001-v1-Fix-FK-violation-when-referencing-a-multi-level-part.patch (text/x-patch, 0B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: jgdr@dalibo.com
Subject: Re: Another FK violation when referencing a multi-level partitioned table
In-Reply-To: <20200206004948.238352db@firost>
References: <20200206004948.238352db@firost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox