Обсуждение: BUG #16621: Unexpected Foreign Key Constraint Violation when Creating New Child Partition
BUG #16621: Unexpected Foreign Key Constraint Violation when Creating New Child Partition
От
PG Bug reporting form
Дата:
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