Re: Guarantee order of batched pg_advisory_xact_lock

On 2/17/26 15:55, Greg Sabino Mullane wrote:
> On Mon, Feb 16, 2026 at 12:45 PM Nico Heller <nico(dot)heller(at)posteo(dot)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
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>