MIME-Version: 1.0
References: <CA+f_mLPhCa_G3nRrB4=e3trqCSQ88x56ZvfeweH-AhwS77xEGg@mail.gmail.com>
 <928c165be24ee307c2fbaa29a2c6360eb8a00e02.camel@cybertec.at>
 <CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@mail.gmail.com> <f68465771b664815c47d50bad814db2ba4e4e9ba.camel@cybertec.at>
In-Reply-To: <f68465771b664815c47d50bad814db2ba4e4e9ba.camel@cybertec.at>
From: Bolaji Wahab <bolajiwahab23@gmail.com>
Date: Wed, 11 Dec 2024 07:46:11 +0100
Message-ID: <CA+f_mLOkQkc=1crYS0mEK0nyw=8=ogGEXAKiLqu+UqFdbDDJ9Q@mail.gmail.com>
Subject: Re: Clarification of behaviour when dropping partitions
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b1d34f0628f8f4a4"
Archived-At: <https://www.postgresql.org/message-id/CA%2Bf_mLOkQkc%3D1crYS0mEK0nyw%3D8%3DogGEXAKiLqu%2BUqFdbDDJ9Q%40mail.gmail.com>
Precedence: bulk

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

Yes, right. I wonder if the team sees an opportunity for some optimization
here, supporting such a scenario efficiently. I can't think of any
downsides to it but I may be missing something.

Cheers.

On Thu, Dec 5, 2024 at 2:38=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.=
at>
wrote:

> On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> > On Wed, Dec 4, 2024 at 6:20=E2=80=AFPM Laurenz Albe <laurenz.albe@cyber=
tec.at>
> wrote:
> > > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > > > I have these two partitioned tables, with referential integrity. Th=
e
> tables
> > > > are structured in such a way that we have 1 to 1 mapping between
> their
> > > > partitions. This is achieved with a foreign key.
> > >
> > > I recommend that you don't create the foreign key constraint between
> the
> > > partitioned tables, but between the individual partitions.
> > >
> > > That will make detaching and dropping partitions easier, and you will
> have
> > > the same integrity guarantees.
> >
> > Yes, this is what I have done.
> > But the whole point of declaring the foreign key constraint on the
> partitioned
> > table is to have it automatically created on subsequent/future
> partitions.
>
> Sure, but then you have to accept the disadvantage that it becomes more
> difficult to detach partitions.  I think it is less pain to create the
> constraint on the partition level.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr"><div>Yes, right. I wonder if the team sees=C2=A0an opportu=
nity=C2=A0for some optimization here,=C2=A0supporting such a scenario effic=
iently. I can&#39;t think of any downsides to it but I may be missing=C2=A0=
something.</div><div><br></div>Cheers.<div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Dec 5, 2=
024 at 2:38=E2=80=AFAM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybe=
rtec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">On Wed, 2024-12-04 at 23:00 +0100, Bolaji =
Wahab wrote:<br>
&gt; On Wed, Dec 4, 2024 at 6:20=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mai=
lto:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybertec.at</a=
>&gt; wrote:<br>
&gt; &gt; On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:<br>
&gt; &gt; &gt; I have these two partitioned tables, with=C2=A0referential i=
ntegrity. The tables<br>
&gt; &gt; &gt; are structured=C2=A0in such=C2=A0a way that we have 1 to 1 m=
apping between their<br>
&gt; &gt; &gt; partitions. This is achieved with a foreign key.<br>
&gt; &gt; <br>
&gt; &gt; I recommend that you don&#39;t create the foreign key constraint =
between the<br>
&gt; &gt; partitioned tables, but between the individual partitions.<br>
&gt; &gt; <br>
&gt; &gt; That will make detaching and dropping partitions easier, and you =
will have<br>
&gt; &gt; the same integrity guarantees.<br>
&gt; <br>
&gt; Yes, this is what I have done.<br>
&gt; But the whole point of declaring the foreign key constraint on the par=
titioned<br>
&gt; table is to have it automatically created on subsequent/future partiti=
ons.<br>
<br>
Sure, but then you have to accept the disadvantage that it becomes more<br>
difficult to detach partitions.=C2=A0 I think it is less pain to create the=
<br>
constraint on the partition level.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div></div></div>

--000000000000b1d34f0628f8f4a4--