public inbox for [email protected]help / color / mirror / Atom feed
Re: 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]> 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-12 11:18 Nico Heller <[email protected]> parent: Tom Lane <[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-12 14:47 Greg Sabino Mullane <[email protected]> parent: Nico Heller <[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-16 17:45 Nico Heller <[email protected]> parent: Nico Heller <[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-17 14:55 Greg Sabino Mullane <[email protected]> parent: 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-18 21:19 Nico Heller <[email protected]> parent: Greg Sabino Mullane <[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