Re: Guarantee order of batched pg_advisory_xact_lock

Nico Heller <nico(dot)heller(at)posteo(dot)de> writes:
> We use the following bulk query as we sometimes need acquire multiple
> locks at the same time and want to avoid round-trips to the database:

> |WITH keys(key) AS (SELECT unnest(:keysToLock)) SELECT
> pg_advisory_xact_lock(hashtextextended(key, 0)) FROM keys|

> :keysToLock is a text[] parameter which is pre-sorted in our
> application. This pre-sorting is done to prevent dead locks when two
> concurrent transactions try acquire the same advisory locks (e.g.
> [a,b,c] [b,a,c] can easily deadlock).
> We thought this would be enough, but we occasionally still run into
> deadlocks.

Have you eliminated the possibility that you're getting hash
collisions? With or without that CTE, I can't see a reason for
PG to change the order in which the unnest() results are processed,
so I think you are barking up the wrong tree about where the
problem is.

regards, tom lane