Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vsoxV-002VuC-0s for pgsql-general@arkaria.postgresql.org; Wed, 18 Feb 2026 21:19:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vsoxU-000FUf-0o for pgsql-general@arkaria.postgresql.org; Wed, 18 Feb 2026 21:19:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vsoxT-000FUX-2P for pgsql-general@lists.postgresql.org; Wed, 18 Feb 2026 21:19:43 +0000 Received: from mout02.posteo.de ([185.67.36.66]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vsoxP-0000000020K-31nF for pgsql-general@postgresql.org; Wed, 18 Feb 2026 21:19:42 +0000 Received: from submission (posteo.de [185.67.36.169]) by mout02.posteo.de (Postfix) with ESMTPS id C16DB240101 for ; Wed, 18 Feb 2026 22:19:36 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=posteo.de; s=2017; t=1771449576; bh=PYgN4wARyspS5io+bYtZ4YqeSOuO8Rxy+S+x7DQoP2o=; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:Cc:From: From; b=o0nZCkJ4rHxfukTKcFgPvhyXaaPqskY2UI5M3R9/UcIXq5U/EstxeFf9918n6HrOa xCOxrQKxkSK+gB4osBTDTc5r73NFt5rvdF5oOfNe6hE5vJmhv8LwmdnKLK6gaUDJdP ItFKCpgLMSDss1yTcEC7DFIdGf2TB1dN1Sj3x9ZtDdljVluBIJPwniCBv+w/IFBCgz iEn7VPDdlUWpNSAO3N+B3tB34Ok+w4PA+0wdxnpNq0wk4XQJhFZCMg09t4SQ8guAnP GpLcX7D7kXvImD38Z6DoniMln/zragJTgbXoBxFNs5eW4BhF2nr+t7PcavZwN5YDET b+Ci/H252VKEw== Received: from customer (localhost [127.0.0.1]) by submission (posteo.de) with ESMTPSA id 4fGTsz5rcYz6tvs; Wed, 18 Feb 2026 22:19:35 +0100 (CET) Content-Type: multipart/alternative; boundary="------------O0L8Vss8LnrvwIHgWIdhgB8K" Message-ID: <0b0c6412-9191-4804-8df7-fc9421ef245d@posteo.de> Date: Wed, 18 Feb 2026 21:19:36 +0000 MIME-Version: 1.0 Subject: Re: Guarantee order of batched pg_advisory_xact_lock To: Greg Sabino Mullane Cc: pgsql-general@postgresql.org References: <93fb937f-9f4d-4bd7-b7e6-51d5f98859d9@posteo.de> <14506.1770844674@sss.pgh.pa.us> <101916.1770850197@sss.pgh.pa.us> <777cfa1c-fd1f-479f-b9b8-217b0f7a40b7@posteo.de> <87ca8c2c-6826-44d1-8a31-a5f793a2729e@posteo.de> Content-Language: en-US From: Nico Heller In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------O0L8Vss8LnrvwIHgWIdhgB8K Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2/17/26 15:55, Greg Sabino Mullane wrote: > On Mon, Feb 16, 2026 at 12:45 PM Nico Heller > wrote: > > 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. > > > Well, you could set log_statement to 'all' for a bit to see *exactly* > what each of the deadlocking processes are doing. Alternatively, > perhaps you can write a hashextendedkey() function that outputs > arguments and results to a log and/or a table. I will give that a shot, thank you > > keysToLock is a text[] parameter which is pre-sorted in our > application > > > Would not hurt to triple-check this part as well. Could show us the > app code? Maybe put in some sort of global assert in the app to verify > that things are indeed sorted as you think they are. I am 100% sure this is the case, as the code base isn't huge and we have central component which is *always* used to acquire advisory locks. It looks as follows, in pseudo Kotlin code: class LockingRepository(val sqlClient: SqlClient) {     @Transactional(propagation = MANDATORY) // enforces a transaction is already active, using pg_advisory_xact_lock is senseless otherwise     fun acquireLocks(keys: List) = sqlClient.query().param(:keys, sort(keys)).execute()     private fun sort(keys: List) = keys.sort() // as I said, it's sorted in some arbitrary way } > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > --------------O0L8Vss8LnrvwIHgWIdhgB8K Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 2/17/26 15:55, Greg Sabino Mullane wrote:
On Mon, Feb 16, 2026 at 12:45 PM Nico Heller <nico.heller@posteo.de> wrote:

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.


Well, you could set log_statement to 'all' for a bit to see *exactly* what each of the deadlocking processes are doing. Alternatively, perhaps you can write a hashextendedkey() function that outputs arguments and results to a log and/or a table.
I will give that a shot, thank you

keysToLock is a text[] parameter which is pre-sorted in our application

Would not hurt to triple-check this part as well. Could show us the app code? Maybe put in some sort of global assert in the app to verify that things are indeed sorted as you think they are.
I am 100% sure this is the case, as the code base isn't huge and we have central component which is always used to acquire advisory locks.
It looks as follows, in pseudo Kotlin code:

class LockingRepository(val sqlClient: SqlClient) {

    @Transactional(propagation = MANDATORY) // enforces a transaction is already active, using pg_advisory_xact_lock is senseless otherwise
    fun acquireLocks(keys: List<String>) = sqlClient.query(<QUERY_HERE>).param(:keys, sort(keys)).execute()
   
    private fun sort(keys: List<String>) = keys.sort() // as I said, it's sorted in some arbitrary way

}

Cheers,
Greg

--
Enterprise Postgres Software Products & Tech Support

--------------O0L8Vss8LnrvwIHgWIdhgB8K--