Обсуждение: PostgreSQL v11.3 - Records are deleted from child table if partitionkey of parent table is changed
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
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. --.
On 2019-Jun-20, Rozboril, Robert wrote: > 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; Hmm, yeah, I wouldn't expect this to work well -- the UPDATEs done by the cascade action would not see that there is another partition. If you were declaring the PK and FK constraints on the parent tablej, it should work correctly: ALTER TABLE pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id); ALTER TABLE pol ADD CONSTRAINT pol_po_id_fk FOREIGN KEY (po_id) REFERENCES po(po_id) ON DELETE CASCADE; (similarly for the "po" table). -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
RE: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed
Hello,
Before I raised this bug I tried also V11 notation but result was the same.
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 po ADD CONSTRAINT po_pkey PRIMARY KEY (po_id, archived);
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 pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id, archived);
ALTER TABLE polf ADD CONSTRAINT polf_pof_id_fk FOREIGN KEY (po_id, archived) REFERENCES pof(po_id, archived) ON DELETE
CASCADE;
ALTER TABLE polt ADD CONSTRAINT polt_pot_id_fk FOREIGN KEY (po_id, archived) REFERENCES pot(po_id, archived) ON DELETE
CASCADE;
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
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
That way as you suggested to create PKs and FKs cannot be done because PK must contain partition key and foreign keys
referencingpartitioned tables are not supported.
Anyway data cannot be deleted from child table in both cases (V10 or V11 notation).
Regards,
Robert
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Thursday, June 20, 2019 20:01
To: Rozboril, Robert <robert.rozboril@dxc.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed
On 2019-Jun-20, Rozboril, Robert wrote:
> 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;
Hmm, yeah, I wouldn't expect this to work well -- the UPDATEs done by the cascade action would not see that there is
anotherpartition. If you were declaring the PK and FK constraints on the parent tablej, it should work correctly:
ALTER TABLE pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id); ALTER TABLE pol ADD CONSTRAINT pol_po_id_fk FOREIGN KEY
(po_id)REFERENCES po(po_id) ON DELETE CASCADE; (similarly for the "po" table).
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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,
maycontain 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
recipientof this message, you are not authorized to read, print, retain, copy or disseminate any part of this message.
Ifyou have received this message in error, please destroy and delete all copies and notify the sender by return e-mail.
Regardlessof content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any
orderor other contract unless pursuant to explicit written agreement or government initiative expressly permitting the
useof e-mail for such purpose. --.
Hi Robert, AFAIU the problem is that when the pof tuple migrates to pot, the partition-level FK constraint on polf sees the update as a delete (because at that level that's what it is) and thus cascades to delete the tuple on polf. There is nothing that automagically instantiates a new tuple in polt. To fix this problem, we would need a mechanism to insert an equivalent referencing tuple in the other partition. I'm not sure how would we achieve such a thing. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
RE: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed
Hi Álvaro, Thank you for your answer. Good luck! Meanwhile I did workaround using before update trigger to handle data in child tables and setting constraints as deferredto don't lose data. I am testing it right now. Regards, Robert -----Original Message----- From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com] Sent: Friday, June 21, 2019 15:28 To: Rozboril, Robert <robert.rozboril@dxc.com> Cc: pgsql-bugs@lists.postgresql.org Subject: Re: PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed Hi Robert, AFAIU the problem is that when the pof tuple migrates to pot, the partition-level FK constraint on polf sees the update asa delete (because at that level that's what it is) and thus cascades to delete the tuple on polf. There is nothing thatautomagically instantiates a new tuple in polt. To fix this problem, we would need a mechanism to insert an equivalent referencing tuple in the other partition. I'm notsure how would we achieve such a thing. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 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, maycontain 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 recipientof this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. Ifyou have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardlessof content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any orderor other contract unless pursuant to explicit written agreement or government initiative expressly permitting the useof e-mail for such purpose. --.