From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [BUG] parenting a PK constraint to a self-FK one (Was: Self FK oddity when attaching a partition)
Date: Fri, 7 Oct 2022 19:53:55 +0200
Message-ID: <20221007175355.2ldgha4mmzewvauu@alvherre.pgsql> (raw)
In-Reply-To: <20221005164048.exmpnpqzfkrgmqtx@alvherre.pgsql>
On 2022-Oct-05, Alvaro Herrera wrote:
> Backpatching this to 12 shows yet another problem -- the topmost
> relation acquires additional FK constraints, not yet sure why. I think
> we must have fixed something in 13 that wasn't backpatched, but I can't
> remember what it is and whether it was intentionally not backpatched.
This was actually a mismerge. Once I fixed that, it worked properly.
However, there was another bug, which only showed up when I did a
DETACH, ATTACH, and repeat. The problem is that when we detach, the
no-longer-partition retains an FK constraint to the partitioned table.
This is good -- we want that one -- but when we reattach, then we see
that the partitioned table is being referenced from outside, so we
consider that another constraint that we need to add the partition to,
*in addition to the constraint that we need to clone*. So we need to
ignore both a self-referencing FK that goes to the partitioned table, as
well as a self-referencing one that comes from the partition-to-be.
When we do that, then the clone correctly uses that one as the
constraint to retain and attach into the hierarchy of constraints, and
everything [appears to] work correctly.
So I've pushed this, and things are now mostly good. Two problems
remain, though I don't think either of them is terribly serious:
1. one of the constraints in the final hierarchy is marked as not
validated. I mentioned this before.
2. (only in 15) There are useless pg_depend rows for the pg_trigger
rows, which make them depend on their parent pg_trigger rows. This is
not related to self-referencing foreign keys, but I just happened to
notice because I was examining the catalog contents with the added test
case. I think this breakage is due to f4566345cf40. I couldn't find
any actual misbehavior caused by these extra pg_depend entries, but we
should not be creating them anyway.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Por suerte hoy explotó el califont porque si no me habría muerto
de aburrido" (Papelucho)
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, jgdr@dalibo.com, zyu@yugabyte.com
Subject: Re: [BUG] parenting a PK constraint to a self-FK one (Was: Self FK oddity when attaching a partition)
In-Reply-To: <20221007175355.2ldgha4mmzewvauu@alvherre.pgsql>
* 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