PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed

Hi guys,

I think I found the bug: Records are deleted from child table if partition key of parent table is changed. Update operation cannot cause data loss.

Record in parent table is properly moved to correct partition but related record in child table is deleted if foreign key allows cascade deletion.

Simplified example:
Parent table - po (purchase order)
Child table - pol (purchase order line)

I want to archive purchase order with purchase order line but purchase order line is lost when purchase order archived flag (partition key) is changed to true.

Sequence of steps:

1. Creation of parent table
CREATE TABLE public.po (
po_id int8 NOT NULL,
remark varchar(1000) NULL,
archived bool NOT NULL
) PARTITION BY LIST (archived);

CREATE TABLE pot PARTITION OF po FOR VALUES IN ('TRUE');
CREATE TABLE pof PARTITION OF po FOR VALUES IN ('FALSE');

ALTER TABLE pof ADD CONSTRAINT pof_pkey PRIMARY KEY (po_id);
ALTER TABLE pot ADD CONSTRAINT pot_pkey PRIMARY KEY (po_id);

2. Creation of child table
CREATE TABLE public.pol (
pol_id int8 NOT NULL,
po_id int8 NOT NULL,
remark varchar(1000) NULL,
archived bool NOT NULL
) PARTITION BY LIST (archived);

CREATE TABLE polt PARTITION OF pol FOR VALUES IN ('TRUE');
CREATE TABLE polf PARTITION OF pol FOR VALUES IN ('FALSE');

ALTER TABLE polf ADD CONSTRAINT polf_pkey PRIMARY KEY (pol_id);
ALTER TABLE polt ADD CONSTRAINT polt_pkey PRIMARY KEY (pol_id);

ALTER TABLE polf ADD CONSTRAINT polf_po_id_fk FOREIGN KEY (po_id) REFERENCES pof(po_id) ON DELETE CASCADE;
ALTER TABLE polt ADD CONSTRAINT polt_po_id_fk FOREIGN KEY (po_id) REFERENCES pot(po_id) ON DELETE CASCADE;

3. Data insertion
INSERT INTO po VALUES (1, '1', FALSE);
INSERT INTO pol VALUES (1, 1, '1', FALSE);

SELECT count(1) FROM po; -- 1
SELECT count(1) FROM pol; -- 1

4. Archiving of purchase order (child record is deleted on update)
UPDATE po
SET archived = TRUE
WHERE po_id = 1;

SELECT count(1) FROM po; -- 1
SELECT count(1) FROM pol; -- 0 - records were deleted and not moved to correct partition

Expected output is that child record will be moved to correct partition.

PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit

Thank you.

Regards,

Robert Rozboril
Technology Consultant, Foundation Engineering, Virtual Private Cloud

DXC Technology
Galvaniho 15, 820 02 Bratislava
Slovakia

dxc.technology<http://www.dxc.technology/> / LinkedIn<https://www.linkedin.com/company/dxctechnology>

DXC Technology Company -- This message is transmitted to you by or on behalf of DXC Technology Company or one of its affiliates. It is intended exclusively for the addressee. The substance of this message, along with any attachments, may contain proprietary, confidential or privileged information or information that is otherwise legally exempt from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. If you have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardless of content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. --.