Feedback-ID: ia2694551:Fastmail
Date: Fri, 7 Oct 2022 19:53:55 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: Zhihong Yu <zyu@yugabyte.com>,
	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)
Message-ID: <20221007175355.2ldgha4mmzewvauu@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20221005164048.exmpnpqzfkrgmqtx@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/20221007175355.2ldgha4mmzewvauu%40alvherre.pgsql>
Precedence: bulk

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)