Content-Type: multipart/alternative;
 boundary="------------xw9oP2bYBV8MqVnR0qcX4w2Q"
Message-ID: <777cfa1c-fd1f-479f-b9b8-217b0f7a40b7@posteo.de>
Date: Thu, 12 Feb 2026 11:18:34 +0000
MIME-Version: 1.0
Subject: Re: Guarantee order of batched pg_advisory_xact_lock
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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>
Content-Language: en-US
From: Nico Heller <nico.heller@posteo.de>
In-Reply-To: <101916.1770850197@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/777cfa1c-fd1f-479f-b9b8-217b0f7a40b7%40posteo.de>
Precedence: bulk

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

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
--------------xw9oP2bYBV8MqVnR0qcX4w2Q
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>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">&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>
  </body>
</html>

--------------xw9oP2bYBV8MqVnR0qcX4w2Q--