From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: David Rowley <dgrowleyml@gmail.com>
Cc: alex.stilwell@macquarie.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
Date: Fri, 30 Apr 2021 13:56:11 -0400
Message-ID: <20210430175611.GA6249@alvherre.pgsql> (raw)
In-Reply-To: <CAApHDvru2vxkNFqyr-UkiM7UV48hDSL_Mn1WgUGZXTf1mSp+rQ@mail.gmail.com>
On 2021-Mar-02, David Rowley wrote:
> Here's a simplifed test case:
>
> create table p(a int primary key) partition by list(a);
> create table p1 partition of p for values in(1);
> create table z(a int primary key references p) partition by list(a);
> create table z1 partition of z for values in(1);
> insert into p values(1);
> insert into z values(1);
> alter table z detach partition z1;
> alter table p detach partition p1;
> alter table z attach partition z1 for values in(1); -- shouldn't be allowed.
Hmm, I agree that this is misbehaving. However ...
> But it is allowed as CloneFkReferencing() calls
> tryAttachPartitionForeignKey() which checks for an existing foreign
> key to the table. It happens to find one.
>
> \d z1
> Table "public.z1"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> a | integer | | not null |
> Indexes:
> "z1_pkey" PRIMARY KEY, btree (a)
> Foreign-key constraints:
> "z_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
>
> I imagine that this foreign key should have been removed when z1 was
> detached from z. If it's not then since when we detach p1 from p, we
> can only check currently attached partitions on referencing tables to
> see if there would be any FK violations.
... I'm not sure that we need to remove the FK from 'z1'; we know well
enough that the constraint still stands. The problem IMO occurs when we
detach p1 -- that's the step that causes the FK go wrong. I think that
DETACH step should throw the error that there are tables referencing the
values in the partition. This works correctly when 'z' is a plain
table, so what is different regarding the constraint in 'z1' (which is
now a plain table)? I'll have a look.
--
Álvaro Herrera 39°49'30"S 73°17'W
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-bugs@postgresql.org
Cc: alvherre@alvh.no-ip.org, dgrowleyml@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
In-Reply-To: <20210430175611.GA6249@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