Question Details

No question body available.

Tags

sql postgresql locking

Answers (1)

February 11, 2026 Score: 1 Rep: 258,243 Quality: Medium Completeness: 60%

If you add an optimizer fence against flattening the subquery, the query should do what you want:

SELECT pgadvisoryxactlock(hashtextextended(orderedkeys.key, 0))
FROM (SELECT 
      FROM unnest(?) WITH ORDINALITY AS keys(key, index)
      ORDER BY index
      / a no-op, but it prevents subquery flattening */
      OFFSET 0) AS orderedkeys;

That is not because the SQL standard says so (advisory locks, and locks in general are not a subject of the standard), but because that's how PostgreSQL works. To be certain, look at the EXPLAIN output to make sure that sorting happens before locking.

Edit: I looked at the source, and actually neither the ORDER BY nor the WITH ORDINALITY are necessary, the way that arrays are implemented now. So the following will also work:

SELECT pgadvisoryxactlock(hashtextextended(keys.key, 0))
FROM unnest(?) keys(key)

But if you want to be less dependent on the current way that arrays are implemented in PostgreSQL, you might still prefer the explicit ordering above.