MIME-Version: 1.0
References: <CAKE1AiY17RgcKCFba1N6Sz6SjHqSqvq+cfKWBfyKFEjT-L+qkg@mail.gmail.com>
 <1529239.1752161326@sss.pgh.pa.us>
In-Reply-To: <1529239.1752161326@sss.pgh.pa.us>
From: Steve Baldwin <steve.baldwin@gmail.com>
Date: Fri, 11 Jul 2025 06:32:45 +1000
Message-ID: <CAKE1AiYMY2wk5BhACRvFhyNoKhJ9thhfaQ07DnFO-Ogg0oeENw@mail.gmail.com>
Subject: Re: Lock timeout in commit
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000043f5750639991957"
Archived-At: <https://www.postgresql.org/message-id/CAKE1AiYMY2wk5BhACRvFhyNoKhJ9thhfaQ07DnFO-Ogg0oeENw%40mail.gmail.com>
Precedence: bulk

--00000000000043f5750639991957
Content-Type: text/plain; charset="UTF-8"

On Fri, 11 Jul 2025 at 01:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>
> I think all you could do is monitor the pg_locks view and hope to
> catch the process in "waiting" state before it fails.
>
> It occurs to me to wonder though if we couldn't provide more
> context in the error about what lock is being waited for.
>
> Thanks Tom !!

The application is an API server so we intentionally set the lock timeout
to a very short interval (5 ms). Having locking context would be great.

Other than deferred FK constraints, what other locking actions are deferred
to commit time?

Cheers,

Steve

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, 11 Jul 2025 at 01:28, Tom Lane &l=
t;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</di=
v><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><br>
I think all you could do is monitor the pg_locks view and hope to<br>
catch the process in &quot;waiting&quot; state before it fails.<br>
<br>
It occurs to me to wonder though if we couldn&#39;t provide more<br>
context in the error about what lock is being waited for.<br><br></blockquo=
te><div>Thanks Tom !!</div><div><br></div><div>The application is an API se=
rver so we intentionally set the lock timeout to a very short interval (5 m=
s). Having locking context would be great.</div><div><br></div><div>Other t=
han deferred FK constraints, what other locking actions are deferred to com=
mit time?</div><div><br></div><div>Cheers,</div><div><br></div><div>Steve</=
div></div></div>

--00000000000043f5750639991957--