Re: Guarantee order of batched pg_advisory_xact_lock

Nico Heller <nico.heller@posteo.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