I recently upgraded my postgres db from 9.5.4 to 10.7 and noticed some odd behavior with an existing query.
The trimmed down version looks like this:
update
mytable
set
job_id = 6
where
id in (
select * from
(
select id from
mytable
where job_id is null
limit 2
) x for update)
and job_id is null
I would expect the number of rows to be updated to equal 2, but instead it is updating all the records that match the subquery without the limit. If I remove the for update
statement, or the matching job_id is null
statement, the records updated does equal 2 as expected. Before we updated this query would update the correct number of rows.
Did some behavior in 10.x change?