3

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?

Ian Burns
  • 31
  • 2

0 Answers0