MIME-Version: 1.0
References: <CAB+=1TVS7o=uBorTNjXHzhLO3pJQOwZOYDWoGMJFqLTc9S8ZRw@mail.gmail.com>
In-Reply-To: <CAB+=1TVS7o=uBorTNjXHzhLO3pJQOwZOYDWoGMJFqLTc9S8ZRw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 29 Apr 2026 23:39:03 -0400
Message-ID: <CANzqJaARrCPLu4W+zJMRRhu4zTR-+Fdnij+04Jeqah9HZVJRMQ@mail.gmail.com>
Subject: Re: Issue during partition drop
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000058d2a40650a535f9"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaARrCPLu4W%2BzJMRRhu4zTR-%2BFdnij%2B04Jeqah9HZVJRMQ%40mail.gmail.com>
Precedence: bulk

--00000000000058d2a40650a535f9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 29, 2026 at 10:37=E2=80=AFPM veem v <veema0000@gmail.com> wrote=
:

> Hi,
> We have the Aurora Postgres database. And for a table with PK-FK
> relationships, we have been running into issues while dropping partitions
> using partman. We have planned to detach and drop the partitions but end =
up
> with the below error , so wanted to understand, if this is expected
> behaviour
>

Absolutely.


> and how to handle it?
>
> ERROR:  cannot drop table <table_name>_p20250202 because other objects
> depend on it
> CONTEXT: SQL statement "DROP TABLE <table_name>_p20250202"
> PL/pgSQL function
> drop_partition_time(text,interval,boolean,boolean,text,timestamp with tim=
e
> zone) line 250 at EXECUTE
> PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 336
> at assignment
> DETAIL: constraint <constraint_name>_fkey on table <table_name> depends o=
n
> table <table_name>_p20250202
>

Whoever designed your database determined that "orphaned child" records are
a bad thing.  <constraint_name>_fkey ensures that every "child" record has
a "parent" record.

If PG allows you to drop <table_name>_p20250202, then there will be
"orphaned children" in your database, but the FK means you don't want
orphaned children.

In order to drop <table_name>_p20250202, you must first "dispose of" (aka
DELETE) the child records that depend on records in
<table_name>_p20250202.  If <constraint_name>_fkey points to a table that
is partitioned the same way that <table_name>_p20250202 is partitioned,
then *maybe* you can DETACH and then DROP that table full of child records.

Only then can you drop <table_name>_p20250202.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000058d2a40650a535f9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Apr 29, 2026 at 10:37=E2=80=AFPM =
veem v &lt;<a href=3D"mailto:veema0000@gmail.com">veema0000@gmail.com</a>&g=
t; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi,<br>We have the =
Aurora Postgres database. And for a table with PK-FK relationships, we have=
 been running into issues while dropping partitions using partman.=C2=A0We =
have planned to detach and drop the partitions but end up with the below er=
ror , so wanted to understand, if this is expected behaviour </div></blockq=
uote><div><br></div><div>Absolutely.</div><div>=C2=A0</div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex"><div dir=3D"ltr">and how to handle it?<br=
>=C2=A0<br>ERROR:=C2=A0 cannot drop table &lt;table_name&gt;_p20250202 beca=
use other objects depend on it<br>CONTEXT: SQL statement &quot;DROP TABLE &=
lt;table_name&gt;_p20250202&quot;<br>PL/pgSQL function drop_partition_time(=
text,interval,boolean,boolean,text,timestamp with time zone) line 250 at EX=
ECUTE<br>PL/pgSQL function partman.run_maintenance(text,boolean,boolean) li=
ne 336 at assignment<br>DETAIL: constraint &lt;constraint_name&gt;_fkey on =
table &lt;table_name&gt; depends on table &lt;table_name&gt;_p20250202<br><=
/div></blockquote><div><br></div><div>Whoever designed your database determ=
ined that &quot;orphaned child&quot; records are a bad=C2=A0thing.=C2=A0 &l=
t;constraint_name&gt;_fkey ensures that every &quot;child&quot; record has =
a &quot;parent&quot; record.</div><div><br></div><div>If PG allows you to d=
rop &lt;table_name&gt;_p20250202, then there will be &quot;orphaned=C2=A0ch=
ildren&quot; in your database, but the FK means you don&#39;t want orphaned=
 children.</div><div><br></div><div>In order to drop &lt;table_name&gt;_p20=
250202, you must first &quot;dispose of&quot; (aka DELETE) the child record=
s that depend on records in &lt;table_name&gt;_p20250202.=C2=A0 If &lt;cons=
traint_name&gt;_fkey points to a table that is partitioned the same way tha=
t &lt;table_name&gt;_p20250202 is partitioned, then <b>maybe</b>=C2=A0you c=
an DETACH and then DROP that table full of child records.</div><div><br></d=
iv><div>Only then can you drop &lt;table_name&gt;_p20250202.</div><div><br>=
</div></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&g=
t;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><=
div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--00000000000058d2a40650a535f9--