On Fri, Mar 16, 2018 at 8:00 AM, Enrico Thierbach <eno@open-lab.org> wrote:
Hi Melvin, Stephen, hi list,
*FWIW, I really don't understand your need to identify the actual rows that are locked. Once you have identified the query that is causing a block (which is usually due to "Idle in Transaction"), AFAIK the only way to remedy the problem is to kill the offending query, or wait for it to complete. I am not aware of any way available to a user to "unlock" individual rows". Indeed, if you could, it would probably lead to corruption of some form.*
The goal is to run a job queue, with a potentially largish number of workers that feed of the queue. So it would be useful to find out which queue entry is being processed right now (I can easily find out: when a row cannot be read via SKIP UNLOCKED it is locked, and probably being worked upon.) It would also be great to understand which worker holds the lock. The intention is NOT to kill the worker or its query.
You probably considered this but the queuing mechanism I use doesn't hold locks on records during processing. Workers claim tasks by locking them, setting a claimed flag of some sort, the releasing the lock (including worker identity if desired) - repeating the general procedure once completed.
My volume is such that the bloat the extra update causes is not meaningful and is easily handled by (auto-)vacuum.