Reserving jobs hits Postgresq Update..(subquery ..limit 1) bug

[pslavov]

Hi we are using delayed_jobs_active_record 4.1.1 with PostgreSQL 9.6.
We can see that PostgreSQL is updating multiple record in some cases here: https://github.com/collectiveidea/delayed_job_active_record/blob/master/lib/delayed/backend/active_record.rb#L100. We can actually see that there are multiple records updated and returned by the UPDATE...RETURNING * query .

The problem is somewhat described here https://dba.stackexchange.com/questions/69471/postgres-update-limit-1/69497#comment321713_69497 , and basically is that the postgresql planer sometimes decides not to see the limit in the sub-query and uses JOIN which updated all the records that matches the predicates in the sub-query.

Hope this helps.

[albus522]

Do you know of a reliable way to reproduce this? I can't get a test case to fail.