>I guess with your query I can figure out which connection holds a lock, but it seems I cannot correlate those locks to the rows which actually are locked, since
pg_locks seems not to reference this in any way.
Enrico,
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.
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.
BTW, the query I provided WILL work in version 10. The commented section was for v9.1 and prior, as "someone" felt it necessary to rename some fields in pg_stat_activity
and remove/replace another field. Hopefully they will refrain from doing so in the future, as it breaks queries and applications.
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
