procrastinate_fetch_job can end up being slow

[ewjoachim]

Following discussion from Discord starting here and below, it seems we could vastly improve the performance of procrastinate_fetch_job.

The following query plan was posted.

[joaquimds]

Woah, someone with the same name (pretty much) as me has already raised the issue I was about to raise?

Re: the PR, why is it necessary to make the change in manager.py? It looks to me that the existing query will already fallback to a job where jobs.lock IS NULL.

[ewjoachim]

Welcome to the club of Joa(qu|ch)im procrastinators !

Btw, as the repo maintainer, I opened the issue tracking the discussion but @TheNeedForSleep is the one deserving praise for this.

Feel free to chime in in the PR rather than here, the author is more likely to react.

As to myself, I'm sorry but I was much to busy lately to start evaluating this. That said, thank you for your interest and welcome around !

[TheNeedForSleep]

If two or more workers fetch a job with the same lock one of them will have a unique constraint violation on the update part.
If it fails to update due to unique constraint it will retry upto 3 times with fuzzy delay to avoid race condition.

If the worker still fails to get a job it will just get a job where no unique constraint violation can occur e.g. a job without a lock.