On Fri, Mar 16, 2018 at 8:00 AM, Enrico Thierbach <e...@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. David J.
