|From:||David Steele <david(at)pgmasters(dot)net>|
|To:||"Cenkar, Maciej" <mcenkar(at)ebay(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>|
|Subject:||Re: Locking during UPDATE query with SUBSELECT|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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
> 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
|Next Message||Alvaro Herrera||2015-02-25 19:42:13||Re: [postgresql 9.3.5] autovacuums stuck on non-existent pg_toast tables|
|Previous Message||tpham||2015-02-25 19:28:42||[postgresql 9.3.5] autovacuums stuck on non-existent pg_toast tables|