MIME-Version: 1.0
References: <CAB+=1TU3kCsNM7xSHTCCu1AJAbL2-5phVmisLB_uTg-q2wEFvg@mail.gmail.com>
 <c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
In-Reply-To: <c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
From: sud <suds1434@gmail.com>
Date: Tue, 2 Apr 2024 01:41:40 +0530
Message-ID: <CAD=mzVW5ixtcPOXS1R=e1P0D83zKSP6ic38Y5WK9xn+dnn2trg@mail.gmail.com>
Subject: Re: Not able to purge partition
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: veem v <veema0000@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000be238d06150e9987"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVW5ixtcPOXS1R%3De1P0D83zKSP6ic38Y5WK9xn%2Bdnn2trg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Mar 21, 2024 at 6:18=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

>
> > [create some partitions, then drop a partition of the referenced table]
> >
>
> > SQL Error [P0001]: ERROR: cannot drop table
> schema1.test_part_drop_parent_p2024_02_01 because other objects depend on=
 it
> > CONTEXT: SQL statement "DROP TABLE
> schema1.test_part_drop_parent_p2024_02_01"
>
> That's normal.  If you create a foreign key constraint to a partitioned
> table, you
> can no longer drop a partition of the referenced table.
>
> What you *can* do is detach the partition and then drop it, but detatchin=
g
> will

be slow because PostgreSQL has to check for referencing rows.
>


*The best solution is to create the foreign key *not* between the
partitioned*
*tables, but between the individual table partitions.  *

Interesting, even my thought was that the detach+drop parent partition will
only look into the specific child partition but not the whole child table.

However, out of curiosity, does this default foreign key setup i.e. foreign
keys between the table (but not between the partitions) also make the data
load into the child partitions slower ( as it must be then looking and
validating the presence of the keys across all the partitions of the parent
table)?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, Mar 21, 2024 at 6:18=E2=80=AFPM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><br>
&gt; [create some partitions, then drop a partition of the referenced table=
]<br>
&gt;<br>
<br>
&gt; SQL Error [P0001]: ERROR: cannot drop table schema1.test_part_drop_par=
ent_p2024_02_01 because other objects depend on it<br>
&gt; CONTEXT: SQL statement &quot;DROP TABLE schema1.test_part_drop_parent_=
p2024_02_01&quot;<br>
<br>
That&#39;s normal.=C2=A0 If you create a foreign key constraint to a partit=
ioned table, you<br>
can no longer drop a partition of the referenced table.<br>
<br>
What you *can* do is detach the partition and then drop it, but detatching =
will=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">be=
 slow because PostgreSQL has to check for referencing rows.<br></blockquote=
><div><i><br></i></div><i>The best solution is to create the foreign key *n=
ot* between the partitioned<br></i><div><i>tables, but between the individu=
al table partitions.=C2=A0=C2=A0</i>=C2=A0</div><div>=C2=A0</div><div>Inter=
esting, even my thought was that the detach+drop parent partition will only=
 look into the specific child partition but not the whole child table.</div=
><div><br></div><div>However, out of curiosity, does this default foreign k=
ey setup i.e. foreign keys between the table (but not between the partition=
s) also make the data load into the child partitions slower ( as it must be=
 then looking and validating the presence of the keys across all the partit=
ions of the parent table)?<br></div><div>=C2=A0</div></div></div>

--000000000000be238d06150e9987--