From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sorin Mircioiu <sorin.mircioiu@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: PostgreSQL's processes blocking each other are not detected as
 deadlock
In-reply-to: 
 <CAO-WFfkqwkqZ+qj0rqcjjg6UqL_34ZPXgC=fqquqjUfkoPt6Sw@mail.gmail.com>
References: 
 <CAO-WFfkqwkqZ+qj0rqcjjg6UqL_34ZPXgC=fqquqjUfkoPt6Sw@mail.gmail.com>
Comments: In-reply-to Sorin Mircioiu <sorin.mircioiu@gmail.com>
	message dated "Tue, 26 Sep 2023 13:23:48 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <404708.1695736945.1@sss.pgh.pa.us>
Date: Tue, 26 Sep 2023 10:02:25 -0400
Message-ID: <404709.1695736945@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/404709.1695736945%40sss.pgh.pa.us>
Precedence: bulk

Sorin Mircioiu <sorin.mircioiu@gmail.com> writes:
> I need answers to the following questions:

> 1. 13502 is blocked by 21544 and 21544 is blocked by 13502. Why PostgreSQL
> didn't detect this situation as a deadlock ?

You have not demonstrated that these two processes are waiting for
each other, only that they are waiting for something.  The subset
of data you presented is insufficient to conclude that a deadlock
should have been reported.  It does appear that some progress
has been made since the previous deadlock, since neither process
is waiting for the same thing it was waiting on as of the deadlock
report.

In the past we've seen cases where a deadlock existed but the
server could not detect it because one step in the waits-for
loop was implemented by client-side behavior rather than a
server-visible lock request.  Not saying that's happened here,
but you might want to consider your application's behavior
with that idea in mind.

> why is ShareLock used for the locking system in my situation ?

ShareLock on an XID is a different thing from ShareLock on a table.
IIRC we request ShareLock on an XID as a way of waiting for the
transaction using that XID to terminate, which is a need that only
comes up in some edge cases like CREATE INDEX CONCURRENTLY.  Have you
identified exactly what statements those functions are executing?

			regards, tom lane