pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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%40firost

Attachments:

  0001-v1-Fix-FK-violation-when-referencing-a-multi-level-part.patch (text/x-patch, 0B) download

view thread (9+ messages)  latest in thread

Message-ID: <20200206004948.238352db@firost>
Permalink:  ../20200206004948.238352db@firost/
Also on:    postgresql.org/message-id/20200206004948.238352db@firost

 ·  · 

reply

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