Locking during UPDATE query with SUBSELECT

On 2/25/15 10:49 AM, Cenkar, Maciej wrote:

Given PostgreSQL 9.3.5 what is locking strategy when executing query
such as:

UPDATE table SET some_col = some_val WHERE id IN (SELECT id FROM
expensive_query_with_joins).

Is this starting to lock rows after it executed sub-select or is it
locking whole table and then executing select?

This statement will lock rows in the update table as they are returned
from the subquery and modified - only a share lock will be held on the
entire table from the beginning (that just keeps people from modifying
the table while you are using it). If the subquery contains a group by,
order by, or some other clause that requires all the returned rows to be
examined as a whole then the row locks will happen very consecutively,
otherwise they could happen over a longer period of time and the locks
will be held for longer.

Is there any advantage in precomputing ids from nested select to run only

UPDATE table SET some_col = some_val WHERE id IN (precomputed_values)?

If your subquery is very expensive but returns a reasonable number of
rows, then putting the results in a temp table and then updating from
the temp table may allow locks to be held a shorter amount of time. If
your subquery contains a group by, order by, etc. as mentioned above
then I wouldn't bother.

One other thing to mention - since the order of updates cannot be
guaranteed I wouldn't run more than one update like this at the same
time or you might get deadlocks.

--
- David Steele
david@pgmasters.net