Date: Fri, 30 Apr 2021 13:56:11 -0400
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?
Message-ID: <20210430175611.GA6249@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAApHDvru2vxkNFqyr-UkiM7UV48hDSL_Mn1WgUGZXTf1mSp+rQ@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210430175611.GA6249%40alvherre.pgsql>
Precedence: bulk

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