public inbox for [email protected]
help / color / mirror / Atom feedRe: Guarantee order of batched pg_advisory_xact_lock
6+ messages / 3 participants
[nested] [flat]
* Re: Guarantee order of batched pg_advisory_xact_lock
@ 2026-02-11 22:49 Tom Lane <[email protected]>
2026-02-12 11:18 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-02-11 22:49 UTC (permalink / raw)
To: Nico Heller <[email protected]>; +Cc: pgsql-general
Nico Heller <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
@ 2026-02-12 11:18 ` Nico Heller <[email protected]>
2026-02-12 14:47 ` Re: Guarantee order of batched pg_advisory_xact_lock Greg Sabino Mullane <[email protected]>
2026-02-16 17:45 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Nico Heller @ 2026-02-12 11:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-general
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<[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
2026-02-12 11:18 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
@ 2026-02-12 14:47 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Greg Sabino Mullane @ 2026-02-12 14:47 UTC (permalink / raw)
To: Nico Heller <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general
On Thu, Feb 12, 2026 at 6:18 AM Nico Heller <[email protected]> wrote:
> I just checked for hash collisions with the following query today:
>
> SELECT COUNT(*), hashtextextended(key, 0) FROM
> (
> SELECT key FROM table1
> UNION
>
FWIW, you need UNION ALL, not UNION, if you are trying to detect duplicate
values (hashed or not) across tables.
Cheers,
Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
2026-02-12 11:18 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
@ 2026-02-16 17:45 ` Nico Heller <[email protected]>
2026-02-17 14:55 ` Re: Guarantee order of batched pg_advisory_xact_lock Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Nico Heller @ 2026-02-16 17:45 UTC (permalink / raw)
To: pgsql-general
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<[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
2026-02-12 11:18 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
2026-02-16 17:45 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
@ 2026-02-17 14:55 ` Greg Sabino Mullane <[email protected]>
2026-02-18 21:19 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Sabino Mullane @ 2026-02-17 14:55 UTC (permalink / raw)
To: Nico Heller <[email protected]>; +Cc: pgsql-general
On Mon, Feb 16, 2026 at 12:45 PM Nico Heller <[email protected]> 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.
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.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Guarantee order of batched pg_advisory_xact_lock
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
2026-02-12 11:18 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
2026-02-16 17:45 ` Re: Guarantee order of batched pg_advisory_xact_lock Nico Heller <[email protected]>
2026-02-17 14:55 ` Re: Guarantee order of batched pg_advisory_xact_lock Greg Sabino Mullane <[email protected]>
@ 2026-02-18 21:19 ` Nico Heller <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Nico Heller @ 2026-02-18 21:19 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: pgsql-general
On 2/17/26 15:55, Greg Sabino Mullane wrote:
> On Mon, Feb 16, 2026 at 12:45 PM Nico Heller <[email protected]>
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-02-18 21:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-11 22:49 Re: Guarantee order of batched pg_advisory_xact_lock Tom Lane <[email protected]>
2026-02-12 11:18 ` Nico Heller <[email protected]>
2026-02-12 14:47 ` Greg Sabino Mullane <[email protected]>
2026-02-16 17:45 ` Nico Heller <[email protected]>
2026-02-17 14:55 ` Greg Sabino Mullane <[email protected]>
2026-02-18 21:19 ` Nico Heller <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox