MIME-Version: 1.0
References: <19420-ec7f929dce3a7e14@postgresql.org> <1b32a18349afe49d4cc605a91b8f900e4f9e1ad1.camel@cybertec.at>
In-Reply-To: <1b32a18349afe49d4cc605a91b8f900e4f9e1ad1.camel@cybertec.at>
From: Agnieszka Chodkowska-Gyurics <a.chodkowska@gmail.com>
Date: Fri, 27 Feb 2026 15:50:27 +0100
Message-ID: <CADdpMS-S-zWYQQQ==Ko364mKcUZnD6CPPELA4gU4zjLqLGyrfQ@mail.gmail.com>
Subject: Re: BUG #19420: Zombie FK exists after partition is detached.
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000045357d064bcf5ca7"
Archived-At: <https://www.postgresql.org/message-id/CADdpMS-S-zWYQQQ%3D%3DKo364mKcUZnD6CPPELA4gU4zjLqLGyrfQ%40mail.gmail.com>
Precedence: bulk

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

I'm thinking about that too, but I wanted to make sure there wasn't a
better solution first.

pt., 27 lut 2026 o 15:45 Laurenz Albe <laurenz.albe@cybertec.at> napisa=C5=
=82(a):

> On Fri, 2026-02-27 at 09:09 +0000, PG Bug reporting form wrote:
> > Logged by:          Agnieszka Chodkowska
> > PostgreSQL version: 16.6
> > Operating system:   Linux, Debian 12.2.0 64 bit
> >
> > I have child table  with FK defined as folllows
> >
> > ALTER TABLE IF EXISTS tst.child_test_1
> >     ADD CONSTRAINT child_test_1_parent_id_parent_part_by_fkey FOREIGN K=
EY
> > (parent_id, parent_part_by)
> >     REFERENCES tst.maintenance_test_1_p20260218 (id, part_by) MATCH
> SIMPLE
> >     ON UPDATE NO ACTION
> >     ON DELETE CASCADE;
>
> This is a table partition with a foreign key to another table partition,
> correct?
>
> > I updated configuration according to documentation as follows:
> > retention_keep_table=3Dfalse ,
> > retention_keep_index =3D false
>
> These are not PostgreSQL configuration parameters...
>
> > Somehow postgresql retains the foreign keys of the detached/dropped
> > partitions, to the parent table partitions.
>
> That is as expected.  partitions are tables in their own right and can
> entertain foreign key constraints to other tables, independent of their
> role as partitions of a partitioned table.
>
> > I try the following methods:
> > partman.run_maintenance('tst.child_test_1')
> > partman.run_maintenance()
> > ALTER TABLE tst.child_test_1 DETACH PARTITION <child_test_p_20260218>
> >
> > The error persisted regardless of the method used.
>
> I'd say that you have to drop the foreign key constraint explicitly.
>
> Yours,
> Laurenz Albe
>


--=20
Zapraszam na moj=C4=85 stron=C4=99 www. <http://www.nureczka.pl>

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

<div dir=3D"ltr"><p class=3D"MsoNormal" style=3D"margin:0cm 0cm 8pt;line-he=
ight:107%;font-size:11pt;font-family:Calibri,sans-serif"><span lang=3D"EN">=
I&#39;m thinking
about that too, but I wanted to make sure there wasn&#39;t a better solutio=
n first.</span></p></div><br><div class=3D"gmail_quote gmail_quote_containe=
r"><div dir=3D"ltr" class=3D"gmail_attr">pt., 27 lut 2026 o 15:45=C2=A0Laur=
enz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cyber=
tec.at</a>&gt; napisa=C5=82(a):<br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">On Fri, 2026-02-27 at 09:09 +0000, PG Bug reporting form wr=
ote:<br>
&gt; Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Agnieszka Chodkowska<br>
&gt; PostgreSQL version: 16.6<br>
&gt; Operating system:=C2=A0 =C2=A0Linux, Debian 12.2.0 64 bit<br>
&gt; <br>
&gt; I have child table=C2=A0 with FK defined as folllows<br>
&gt; <br>
&gt; ALTER TABLE IF EXISTS tst.child_test_1<br>
&gt;=C2=A0 =C2=A0 =C2=A0ADD CONSTRAINT child_test_1_parent_id_parent_part_b=
y_fkey FOREIGN KEY<br>
&gt; (parent_id, parent_part_by)<br>
&gt;=C2=A0 =C2=A0 =C2=A0REFERENCES tst.maintenance_test_1_p20260218 (id, pa=
rt_by) MATCH SIMPLE<br>
&gt;=C2=A0 =C2=A0 =C2=A0ON UPDATE NO ACTION<br>
&gt;=C2=A0 =C2=A0 =C2=A0ON DELETE CASCADE;<br>
<br>
This is a table partition with a foreign key to another table partition,<br=
>
correct?<br>
<br>
&gt; I updated configuration according to documentation as follows:<br>
&gt; retention_keep_table=3Dfalse ,<br>
&gt; retention_keep_index =3D false<br>
<br>
These are not PostgreSQL configuration parameters...<br>
<br>
&gt; Somehow postgresql retains the foreign keys of the detached/dropped<br=
>
&gt; partitions, to the parent table partitions.<br>
<br>
That is as expected.=C2=A0 partitions are tables in their own right and can=
<br>
entertain foreign key constraints to other tables, independent of their<br>
role as partitions of a partitioned table.<br>
<br>
&gt; I try the following methods:<br>
&gt; partman.run_maintenance(&#39;tst.child_test_1&#39;)<br>
&gt; partman.run_maintenance()<br>
&gt; ALTER TABLE tst.child_test_1 DETACH PARTITION &lt;child_test_p_2026021=
8&gt;<br>
&gt; <br>
&gt; The error persisted regardless of the method used.<br>
<br>
I&#39;d say that you have to drop the foreign key constraint explicitly.<br=
>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr"><div><div><a href=3D"http://www.nureczka.pl" tar=
get=3D"_blank">Zapraszam na moj=C4=85 stron=C4=99 www.</a><br></div></div><=
/div></div>

--00000000000045357d064bcf5ca7--