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