I think old "sub-FK" should not be dropped, that will be violates foreign key constraint. For example :
postgres=# insert into r values(1,1);
INSERT 0 1
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# delete from p_1 where id = 1;
DELETE 1
postgres=# select * from r_1;
id | p_id
----+------
1 | 1
(1 row)
If I run above SQLs on pg12.12, it will report error below:
postgres=# delete from p_1 where id = 1;
ERROR: update or delete on table "p_1" violates foreign key constraint "r_1_p_id_fkey1" on table "r_1"
DETAIL: Key (id)=(1) is still referenced from table "r_1".
On 2023-Jul-05, Jehan-Guillaume de Rorthais wrote:
> ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
>
> The old sub-FKs (below 18289) created in this table to enforce the action
> triggers on referenced partitions are not deleted when the table becomes a
> partition. Because of this, we have additional and useless triggers on the
> referenced partitions and we can not DETACH this partition on the referencing
> side anymore:
Oh, hm, interesting. Thanks for the report and patch. I found a couple
of minor issues with it (most serious one: nkeys should be 3, not 2;
also sysscan should use conrelid index), but I'll try and complete it so
that it's ready for 2023-08-10's releases.
Regards
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/