BUG #16621: Unexpected Foreign Key Constraint Violation when Creating New Child Partition - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16621
Logged by:          Dylan Mann
Email address:      dylan@quorum.us
PostgreSQL version: 12.4
Operating system:   postgres:12.4 Docker Container (Mac OS)
Description:

Hello,

I am attempting to add a partition to an existing table that is referenced
by a foreign key, but I am getting a constraint violation when trying to
move data into the new partition.  I'm not sure if this is expected
behavior, but I was unable to find any documentation about it, so my
assumption is that it's either an undocumented limitation or a bug.  I have
a workaround (the third transaction shown below), which involves dropping
all foreign key constraints to the partitioned table before inserting the
data and adding them back after inserting the data, but my understanding of
deferred constraints is that they are validated at the end of a transaction,
so this shouldn't be necessary.

Below is a minimal working example, tested on both 12.2 and 12.4.

-- Create initial schema and data
BEGIN;
CREATE TABLE to_table(
    id int,
    partition_key int,
    PRIMARY KEY (id, partition_key)
) PARTITION BY LIST (partition_key);
CREATE TABLE to_table__default PARTITION OF to_table DEFAULT;
INSERT INTO to_table__default VALUES (1, 1), (2, 2);

CREATE TABLE from_table(
    id int,
    partition_key int,
    to_id int,
    FOREIGN KEY (to_id, partition_key) REFERENCES to_table (id,
partition_key) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO from_table VALUES (1, 1, 1), (2, 2, 2);
COMMIT;

-- Add a new partition to to_table, migrating data from the default
partition.  The following command fails on COMMIT with:
-- ERROR:  update or delete on table "to_table__default" violates foreign
key constraint "from_table_to_id_partition_key_fkey1" on table
"from_table"
-- DETAIL:  Key (id, partition_key)=(1, 1) is still referenced from table
"from_table".
BEGIN;
CREATE TABLE to_table__1 ( LIKE to_table );
ALTER TABLE to_table__1 ADD CONSTRAINT to_table__partition_1__check CHECK
(partition_key = 1);

WITH move_tuples AS (
    DELETE FROM to_table__default
    WHERE partition_key = 1
    RETURNING *
) INSERT INTO to_table__1 (
    SELECT *
    FROM move_tuples
);

ALTER TABLE to_table ATTACH PARTITION to_table__1 FOR VALUES IN (1);
COMMIT;


-- The following code works as expected, but requires dropping and
recreating all foreign key constraints
BEGIN;
ALTER TABLE from_table DROP CONSTRAINT
from_table_to_id_partition_key_fkey;
CREATE TABLE to_table__1 ( LIKE to_table );
ALTER TABLE to_table__1 ADD CONSTRAINT to_table__partition_1__check CHECK
(partition_key = 1);

WITH move_tuples AS (
    DELETE FROM to_table__default
    WHERE partition_key = 1
    RETURNING *
) INSERT INTO to_table__1 (
    SELECT *
    FROM move_tuples
);

ALTER TABLE to_table ATTACH PARTITION to_table__1 FOR VALUES IN (1);
ALTER TABLE from_table ADD CONSTRAINT from_table_to_id_partition_key_fkey
FOREIGN KEY (to_id, partition_key) REFERENCES to_table (id, partition_key)
DEFERRABLE INITIALLY DEFERRED;
COMMIT;

Thanks in advance,
Dylan