Greetings Melvin, * Melvin Davidson (melvin6925@xxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
