MIME-Version: 1.0
From: Gaurav Anand <gaurav.anand@saama.com>
Date: Mon, 14 Feb 2022 11:24:17 +0530
Message-ID: 
 <CAMZ99ncALOUj4_MrdLP0OC89Ndjdo+CzNB3gFVHC9EEpKXhj7A@mail.gmail.com>
Subject: PG10 Attach-Detach Partition in Declarative vs Inheritance
To: pgsql-admin@postgresql.org, pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000024545205d7f41016"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMZ99ncALOUj4_MrdLP0OC89Ndjdo%2BCzNB3gFVHC9EEpKXhj7A%40mail.gmail.com>
Precedence: bulk

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

Hi Team,

I need your kind suggestion:
We have Postgres 10 and have partition tables currently with Declarative
partitions. When we refresh the data and detach and attach the partition,
during this time our Application which has Sql query on these tables takes
Lock and sometimes a deadlock situation also arises.

To overcome deadlock we used, advisory lock however the application still
keeps waiting for lock to release.

Please suggest the best way -
1. Will Inheritance partition (in PG10) help avoid the explicit locks.
2. Is upgrading the only choice? if so which version of PG.
3. Please suggest any other work around.

Thanks.

Regards,
Gaurav Anand

[image: logo] <https://bit.ly/3nJh91F>

--=20


This communication is confidential and
subject to and governed by=C2=A0Saama=E2=80=99s=C2=A0
Electronic
Communications Disclaimer.=20
<https://www.saama.com/email-communication-disclaimer/>


=C2=A0

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

<div dir=3D"ltr">Hi Team,<div><br></div><div>I need your kind suggestion:<b=
r>We have Postgres 10 and have partition=C2=A0tables currently with Declara=
tive partitions. When we refresh the data and detach and attach the partiti=
on, during this time our Application which has Sql query on these tables ta=
kes Lock and sometimes a deadlock situation also arises.</div><div>=C2=A0</=
div><div>To overcome deadlock we used, advisory lock however=C2=A0the appli=
cation still keeps waiting for lock to release.</div><div><br></div><div>Pl=
ease suggest the best way -=C2=A0</div><div>1. Will Inheritance partition (=
in PG10) help avoid the explicit=C2=A0locks.</div><div>2. Is upgrading the =
only choice? if so which version of PG.</div><div>3. Please suggest any oth=
er work around.<br clear=3D"all"><div><div dir=3D"ltr" class=3D"gmail_signa=
ture" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"=
ltr"><div><div dir=3D"ltr"><div><br></div><div>Thanks.</div><div><br></div>=
<div>Regards,</div><div>Gaurav Anand</div></div></div></div></div></div><br=
><a href=3D"https://bit.ly/3nJh91F" target=3D"_blank"><img src=3D"https://w=
ww.saama.com/wp-content/uploads/2021/10/Endpoints-News-email-sig-420x84-1.p=
ng" alt=3D"logo"></a></div></div></div></div>

<br>
<p><span style=3D"font-size:10pt;line-height:115%;font-family:Arial,sans-se=
rif">This communication is confidential and
subject to and governed by=C2=A0Saama=E2=80=99s</span><span style=3D"font-s=
ize:9.5pt;line-height:115%;color:#222222;background:white">=C2=A0</span><a =
href=3D"https://www.saama.com/email-communication-disclaimer/" target=3D"_b=
lank"><span style=3D"font-size:10.0pt;line-height:115%;font-family:&quot;Ar=
ial&quot;,&quot;sans-serif&quot;">Electronic
Communications Disclaimer</span><span style=3D"font-size:9.5pt;line-height:=
115%;color:#1155cc;background:white">.</span></a></p>

<p>=C2=A0</p>
--00000000000024545205d7f41016--