RE: PostgreSQL v11.3 - Records are deleted from child table if partition 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 deferred to don't lose data. I am testing it right now.

Regards,
Robert

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)2ndquadrant(dot)com]
Sent: Friday, June 21, 2019 15:28
To: Rozboril, Robert <robert(dot)rozboril(at)dxc(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)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 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

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. --.