From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: tender wang <tndrwang@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Date: Thu, 10 Aug 2023 17:03:45 +0200
Message-ID: <20230810170345.26e41b05@karst> (raw)
In-Reply-To: <20230803090243.5ba3mapddthv6yhn@alvherre.pgsql>
References: <CAHewXNkL1QC_erA_7mkFHHAU9c56fa3oxuSvRt9cmtyZRY8Zhw@mail.gmail.com>
<20230803090243.5ba3mapddthv6yhn@alvherre.pgsql>
On Thu, 3 Aug 2023 11:02:43 +0200
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2023-Aug-03, tender wang wrote:
>
> > I think old "sub-FK" should not be dropped, that will be violates foreign
> > key constraint.
>
> Yeah, I've been playing more with the patch and it is definitely not
> doing the right things. Just eyeballing the contents of pg_trigger and
> pg_constraint for partitions added by ALTER...ATTACH shows that the
> catalog contents are inconsistent with those added by CREATE TABLE
> PARTITION OF.
Well, as stated in my orignal message, at the patch helps understanding the
problem and sketch a possible solution. It definitely is not complete.
After DETACHing the table, we surely needs to check everything again and
recreating what is needed to keep the FK consistent.
But should we keep the FK after DETACH? Did you check the two other discussions
related to FK, self-FK & partition? Unfortunately, as Tender experienced, the
more we dig the more we find bugs. Moreover, the second one might seems
unsolvable and deserve a closer look. See:
* FK broken after DETACHing referencing part
https://www.postgresql.org/message-id/20230420144344.40744130%40karst
* Issue attaching a table to a partitioned table with an auto-referenced
foreign key
https://www.postgresql.org/message-id/20230707175859.17c91538%40karst
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: jgdr@dalibo.com, alvherre@alvh.no-ip.org, tndrwang@gmail.com
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
In-Reply-To: <20230810170345.26e41b05@karst>
* 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