Content-Type: multipart/alternative;
 boundary="------------MWXrHp7g3m4DWuXNBdP4NaNI"
Message-ID: <87ca8c2c-6826-44d1-8a31-a5f793a2729e@posteo.de>
Date: Mon, 16 Feb 2026 17:45:30 +0000
MIME-Version: 1.0
Subject: Re: Guarantee order of batched pg_advisory_xact_lock
From: Nico Heller <nico.heller@posteo.de>
To: pgsql-general@postgresql.org
References: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de>
 <14506.1770844674@sss.pgh.pa.us>
 <b461c689-1d2a-4e20-be33-e75a439d5823@posteo.de>
 <101916.1770850197@sss.pgh.pa.us>
 <777cfa1c-fd1f-479f-b9b8-217b0f7a40b7@posteo.de>
Content-Language: en-US
In-Reply-To: <777cfa1c-fd1f-479f-b9b8-217b0f7a40b7@posteo.de>
Archived-At: <https://www.postgresql.org/message-id/87ca8c2c-6826-44d1-8a31-a5f793a2729e%40posteo.de>
Precedence: bulk

This is a multi-part message in MIME format.
--------------MWXrHp7g3m4DWuXNBdP4NaNI
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Does anyone have any idea what the root cause of my issue is? I 
appreciate any insight.
As I said, hash collisions can be rules out, sadly.

On 2/12/26 12:18, Nico Heller wrote:
>
> I just checked for hash collisions with the following query today:
>
> SELECT COUNT(*), hashtextextended(key, 0) FROM
> (
>   SELECT key FROM table1
>   UNION
>   SELECT key FROM table2
>   UNION
>   ...
> ) keys (key)
> GROUP BY hashtextextended(key, 0)
> HAVING COUNT(*) > 1
>
> Where table1, table2, ... are all the tables we are acquire keys from 
> to use for the mentioned query.
>
> Sadly, no results were returned. Thus, I can rule out hash collisions.
>
> Any other thoughts? Here is an error log from the JDBC driver:
>
>
> 	
> org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: 
> Process 60780 waits for ExclusiveLock on advisory lock 
> [24605,3030106527,494580150,1]; blocked by process 65280.
>  Process 65280 waits for ExclusiveLock on advisory lock 
> [24605,1321834016,1311356115,1]; blocked by process 60780.
>
>
>
> On 2/11/26 23:49, Tom Lane wrote:
>> Nico Heller<nico.heller@posteo.de> writes:
>>> So it would probably be better to ORDER BY the hashtextended result
>>> instead of :keysToLock, right?
>> Yeah, that seems like it'd work, if you have no other dependencies
>> on the locking order.
>>
>> 			regards, tom lane
--------------MWXrHp7g3m4DWuXNBdP4NaNI
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Does anyone have any idea what the root cause of my issue is? I
      appreciate any insight.<br>
      As I said, hash collisions can be rules out, sadly.</p>
    <div class="moz-cite-prefix">On 2/12/26 12:18, Nico Heller wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:777cfa1c-fd1f-479f-b9b8-217b0f7a40b7@posteo.de">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <p>I just checked for hash collisions with the following query
        today:<br>
        <br>
        SELECT COUNT(*), hashtextextended(key, 0) FROM<br>
        (<br>
          SELECT key FROM table1<br>
          UNION<br>
          SELECT key FROM table2<br>
          UNION<br>
          ...<br>
        ) keys (key)<br>
        GROUP BY hashtextextended(key, 0)<br>
        HAVING COUNT(*) &gt; 1<br>
      </p>
      <p>Where table1, table2, ... are all the tables we are acquire
        keys from to use for the mentioned query.<br>
        <br>
        Sadly, no results were returned. Thus, I can rule out hash
        collisions.<br>
        <br>
        Any other thoughts? Here is an error log from the JDBC driver:<br>
      </p>
      <table class="table table-condensed osdDocViewerTable"
        data-test-subj="osdDocViewerTable"
style="text-align: left; text-transform: none; color: rgb(0, 0, 0);">
        <tbody>
          <tr>
            <td class="osdDocViewer__field"
              data-test-subj="osdDocViewerField"
              style="text-align: left; text-transform: none;"><br>
            </td>
            <td class="table table-condensed osdDocViewerTable"
              style="text-align: left; text-transform: none;">
              <div class="table table-condensed osdDocViewerTable"
style="text-align: left; text-transform: none; font-family: Aptos, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">org.postgresql.util.PSQLException:
                ERROR: deadlock detected Detail: Process 60780 waits for
                ExclusiveLock on advisory lock
                [24605,3030106527,494580150,1]; blocked by process
                65280.</div>
              <div class="table table-condensed osdDocViewerTable"
style="text-align: left; text-transform: none; font-family: Aptos, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> 
                                                                       
                                                                     
                 Process 65280 waits for ExclusiveLock on advisory lock
                [24605,1321834016,1311356115,1]; blocked by process
                60780.</div>
            </td>
          </tr>
        </tbody>
      </table>
      <p><br>
        <br>
      </p>
      <div class="moz-cite-prefix">On 2/11/26 23:49, Tom Lane wrote:<br>
      </div>
      <blockquote type="cite" cite="mid:101916.1770850197@sss.pgh.pa.us">
        <pre wrap="" class="moz-quote-pre">Nico Heller <a
        class="moz-txt-link-rfc2396E"
        href="mailto:nico.heller@posteo.de" moz-do-not-send="true">&lt;nico.heller@posteo.de&gt;</a> writes:
</pre>
        <blockquote type="cite">
          <pre wrap="" class="moz-quote-pre">So it would probably be better to ORDER BY the hashtextended result 
instead of :keysToLock, right?
</pre>
        </blockquote>
        <pre wrap="" class="moz-quote-pre">Yeah, that seems like it'd work, if you have no other dependencies
on the locking order.

			regards, tom lane
</pre>
      </blockquote>
    </blockquote>
  </body>
</html>

--------------MWXrHp7g3m4DWuXNBdP4NaNI--