Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Oversight the DetachPartitionFinalize(), I found another bug below:

postgres=# CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id);
CREATE TABLE
postgres=# CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE r_1 (
postgres(#     id   bigint PRIMARY KEY,
postgres(#     p_id bigint NOT NULL
postgres(#   );
CREATE TABLE
postgres=#  CREATE TABLE r (
postgres(#     id   bigint PRIMARY KEY,
postgres(#     p_id bigint NOT NULL,
postgres(#     FOREIGN KEY (p_id) REFERENCES p (id)
postgres(#   ) PARTITION BY list (id);
CREATE TABLE
postgres=# ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
ALTER TABLE
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# insert into r_1 values(1,1);
ERROR:  insert or update on table "r_1" violates foreign key constraint "r_p_id_fkey"
DETAIL:  Key (p_id)=(1) is not present in table "p".

After detach operation, r_1 is normal relation and the inherited foreign key 'r_p_id_fkey' should be removed.

 

tender wang <tndrwang@gmail.com> 于2023年8月3日周四 17:34写道：

I think the code to determine that fk of a partition is inherited or not is not enough. 

For example, in this case, foreign key r_1_p_id_fkey1  is not inherited from parent.

If conform->conparentid(in DetachPartitionFinalize func) is valid, we should recheck confrelid(pg_constraint) field.

I try to fix this problem in the attached patch.

Any thoughts.

Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年8月3日周四 17:02写道：

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.

--
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/