Can not drop partition if exist foreign keys
Hi all.
There are two common practice to drop partition from partitioned table: just drop or detach-drop. But simple drop don't work if exist foreign key. Example script attached.
Attachments:
On 2023-Oct-11, Олег Самойлов wrote:
There are two common practice to drop partition from partitioned
table: just drop or detach-drop. But simple drop don't work if exist
foreign key. Example script attached.
Yeah. Detach it first, then you should be able to drop it.
psql:test.sql:15: ERROR: cannot drop table parent_0 because other objects depend on it
DETAIL: constraint children_id_fkey on table children depends on table parent_0
HINT: Use DROP ... CASCADE to drop the dependent objects too.Looked like a bug.
We tried to make DROP work, but we didn't find a way. Patches welcome.
--
Álvaro Herrera
On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:
There are two common practice to drop partition from partitioned table: just drop or detach-drop.
But simple drop don't work if exist foreign key. Example script attached.
That is working as designed. You cannot detach a partition of a table if a
foreign key points to it.
Create the foreign key constraints between the partitions instead.
Yours,
Laurenz Albe
<div> </div><div> </div><div>11.10.2023, 14:20, "Laurenz Albe" <laurenz.albe@cybertec.at>:</div><blockquote><p>On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:</p><blockquote> There are two common practice to drop partition from partitioned table: just drop or detach-drop.<br /> But simple drop don't work if exist foreign key. Example script attached.</blockquote><p><br />That is working as designed. You cannot detach a partition of a table if a<br />foreign key points to it.</p></blockquote><div>Nope, of cause any can <span style="background-color:transparent">detach a partition of a empty table even if a foreign key points to it.</span></div><div>The problem is other. Documented that there are two variants how drop partition. Just drop or detach then drop. Both work on a simple partition, but only the second variant works in this case.</div>
On Thu, 2023-10-12 at 17:56 +0300, Олег Самойлов wrote:
11.10.2023, 14:20, "Laurenz Albe" <laurenz.albe@cybertec.at>:
On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:
There are two common practice to drop partition from partitioned table: just drop or detach-drop.
But simple drop don't work if exist foreign key. Example script attached.That is working as designed. You cannot detach a partition of a table if a
foreign key points to it.Nope, of cause any can detach a partition of a empty table even if a foreign key points to it.
You are right, and I was wrong. You can detach partitions from tables with a
foreign key pointing to them.
Yours,
Laurenz Albe