Ah I see now, if the node that first tries the job goes down while the job is in progress, other nodes can determine that the job is abandoned by observing that there is a job in the "executing" state that lacks a corresponding lock.
I wonder if this could be achieved by a dedicated table that tracks cluster membership. Each node, when started, inserts a new row into a "nodes" table that contains basically just an id, the node name for debugging, and a alive_at timestamp. The node needs a process that updates the timestamp at some regular interval. When you cut a job, record the id of the node row. Abandoned jobs are any job rows where the timestamp on the corresponding node row alive_at is older than now() - the interval by some appreciable amount. You can garbage collect the "nodes" table when the alive_at value is old and there are no associated jobs in the running or queued states.
Thoughts?
P.S.
To be clear, I don’t have any concerns about the correctness of the current implementation. However I’m just in the unfortunate position of already using advisory locks on 64bit auto incrementing ids. I think advisory locks can be reasonably used by people’s application logic, but in a library it’s tough because it is easy to become incompatible with any other library that also uses them.
