MIME-Version: 1.0
References: 
 <CAHgTRfdEVMi_YVuq3M4R4uARhXQ8ZgkMid3LhxjFbS+Y+Z_8GQ@mail.gmail.com>
 <54994813530a36c0280fd8779e65ad1ad73d910a.camel@cybertec.at>
 <11465.1747315470@sss.pgh.pa.us>
In-Reply-To: <11465.1747315470@sss.pgh.pa.us>
From: James Pang <jamespang886@gmail.com>
Date: Thu, 15 May 2025 21:32:38 +0800
Message-ID: 
 <CAHgTRffkd2uUKxsRKvC3hU-Vje9S=JHxN134YkVN_WhYOw45-g@mail.gmail.com>
Subject: Re: a lot of session wait on lock relation
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b2012b06352cb349"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRffkd2uUKxsRKvC3hU-Vje9S%3DJHxN134YkVN_WhYOw45-g%40mail.gmail.com>
Precedence: bulk

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

thanks, we are checking  partition   maintain jobs ,that hold access
exclusive lock.

Tom Lane <tgl@sss.pgh.pa.us> =E6=96=BC 2025=E5=B9=B45=E6=9C=8815=E6=97=A5=
=E9=80=B1=E5=9B=9B =E4=B8=8B=E5=8D=889:24=E5=AF=AB=E9=81=93=EF=BC=9A

> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:
> >> why inserts into partition table cause "relation lock" ?
>
> > Something else does; use the pg_blocking_pids() function with the
> process ID of
> > a blocked backend to find out who is holding the lock.
>
> More specifically: the inserts are only trying to get a shared lock.
> If they are blocked, it's because some other operation is already
> holding an exclusive lock on the table and is not letting go.
> Look for uncommitted DDL changes.
>
> More details about that at [1].
>
>                         regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TAB=
LES
>
>
>

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

<div dir=3D"ltr">thanks, we are checking=C2=A0 partition=C2=A0 =C2=A0mainta=
in jobs ,that hold access exclusive lock.</div><br><div class=3D"gmail_quot=
e gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">Tom Lane &lt=
;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =E6=96=BC 2=
025=E5=B9=B45=E6=9C=8815=E6=97=A5=E9=80=B1=E5=9B=9B =E4=B8=8B=E5=8D=889:24=
=E5=AF=AB=E9=81=93=EF=BC=9A<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at" ta=
rget=3D"_blank">laurenz.albe@cybertec.at</a>&gt; writes:<br>
&gt; On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:<br>
&gt;&gt; why inserts into partition table cause &quot;relation lock&quot; ?=
<br>
<br>
&gt; Something else does; use the pg_blocking_pids() function with the proc=
ess ID of<br>
&gt; a blocked backend to find out who is holding the lock.<br>
<br>
More specifically: the inserts are only trying to get a shared lock.<br>
If they are blocked, it&#39;s because some other operation is already<br>
holding an exclusive lock on the table and is not letting go.<br>
Look for uncommitted DDL changes.<br>
<br>
More details about that at [1].<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
[1] <a href=3D"https://www.postgresql.org/docs/current/explicit-locking.htm=
l#LOCKING-TABLES" rel=3D"noreferrer" target=3D"_blank">https://www.postgres=
ql.org/docs/current/explicit-locking.html#LOCKING-TABLES</a><br>
<br>
<br>
</blockquote></div>

--000000000000b2012b06352cb349--