Re: SELECT .. FOR UPDATE: find out who locked a row

Greetings Melvin,

* Melvin Davidson (melvin6925(at)gmail(dot)com) wrote:
> >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.
>
> *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.*

No, locks are not able to be released mid-transaction. That said, it
can be difficult sometimes to determine which of the many sessions is
holding a lock on a specific row, hence the two approaches I provided,
which actually address the question which was raised. While the
use-case might not be on completely solid ground here, I don't think
it's entirely unreasonable, so I don't think there's any need to tell
the OP that what they're asking for isn't really what they want, in this
case.

> *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.*

Changes will continue to be made between major versions of PostgreSQL
when they're deemed necessary; I'd suggest those applications be
prepared to adjust on a per-major-version basis when future changes
happen. We do have quite a bit of discussion about changes which are
made and they are not done so without good justification, but they can
and do happen.

Thanks!

Stephen