*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.
With what the conversation brought up here (still trying to catch up with everything) I can:
1) determine all workers that currently are holding a lock (via Melvin’s); 2) on an individual base try to lock the row in a second connection and use a third connection to figure out which worker connection holds a lock on a specific single row (via Stephen’s).
This is probably good enough to cover the necessary basic functionality, so thank you for your input.
Am I correct to assume that there is no other way to determine who is holding a lock on a specific row and/or determine this for many rows in one go?
(I guess I am also correct to assume that whatever the worker is doing there is no way to somehow write this information into the database via the same connection. (Using a second connection would be obviously easy)