Ufdjrw

Why do airplanes bank sharply to the right after air-to-air refueling?

A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See

Is micro rebar a better way to reinforce concrete than rebar?

How to avoid supervisors with prejudiced views?

How to invert MapIndexed on a ragged structure? How to construct a tree from rules?

Which one is the true statement?

Easy to read palindrome checker

Bartok - Syncopation (1): Meaning of notes in between Grand Staff

How a 64-bit process virtual address space is divided in Linux?

Can MTA send mail via a relay without being told so?

Proper way to express "He disappeared them"

Is wanting to ask what to write an indication that you need to change your story?

Flying from Cape Town to England and return to another province

Reference request: Grassmannian and Plucker coordinates in type B, C, D

I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin

What connection does MS Office have to Netscape Navigator?

Find non-case sensitive string in a mixed list of elements?

Why doesn't UK go for the same deal Japan has with EU to resolve Brexit?

Should I cite using beginthebibliography or beginfilecontents*

Is there always a complete, orthogonal set of unitary matrices?

Is it possible to replace duplicates of a character with one character using tr

Is it convenient to ask the journal's editor for two additional days to complete a review?

Why did CATV standarize in 75 ohms and everyone else in 50?

Why is the US ranked as #45 in Press Freedom ratings, despite its extremely permissive free speech laws?

Postgres SubQuery Limit For Update not respecting Limit

2

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?

postgresql sql-update subquery limit select-for-update

share|improve this question

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

add a comment | 

2

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?

postgresql sql-update subquery limit select-for-update

share|improve this question

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

add a comment | 

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?

postgresql sql-update subquery limit select-for-update

share|improve this question

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

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

share|improve this question

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

share|improve this question

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

asked Mar 7 at 16:43

Ian BurnsIan Burns

111

asked Mar 7 at 16:43

Ian BurnsIan Burns

111