Re: Receiving many more rows than expected

On Monday 12 May 2014 10:10:36 David G Johnston wrote:
> Did you try rewriting the query to avoid using an IN expression?
>
> UPDATE foo SET processing = 't'
> FROM (
> SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT 5000 FOR
> UPDATE
> ) src
> WHERE foo.id = src.id;
>
> The workaround I mentioned above said that a CTE was needed but I'm thinking
> that a simply FROM would be just as effective. Otherwise:
>
> UPDATE foo SET processing = 't'
> FROM (
> WITH ids_to_update AS (
> SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT 5000 FOR
> UPDATE
> )
> SELECT id FROM ids_to_update
> ) src
> WHERE foo.id = src.id;

As it happens, I've done a fair bit of refactoring in my code (but not the
actual query), and now I cannot reproduce the bug anymore :/ The refactoring
had to do with taking status queries to a different connection, and changing
the timing of calling the problematic query and interruption by other threads,
to increase throughput.

--
Vincent de Phily