Another FK violation when referencing a multi-level partitioned table

From: Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>
To: <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Another FK violation when referencing a multi-level partitioned table
Date: 2020-02-05 23:49:48
Message-ID: 20200206004948.238352db@firost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
0001-v1-Fix-FK-violation-when-referencing-a-multi-level-part.patch text/x-patch 4.4 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2020-02-06 05:48:46 Re: BUG #16242: convert_tuple_* not handling missing values correctly
Previous Message Alvaro Herrera 2020-02-05 21:13:02 Re: DROP OWNED CASCADE vs Temp tables