PostgreSQL v11.3 - Records are deleted from child table if partitionkey 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 / LinkedIn