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

On Thu, Mar 15, 2018 at 10:14 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

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

> Changes will continue to be made between major versions of PostgreSQL
>when they're deemed necessary; I'

Yes, Stephen, I certainly understand making changes to system catalogs
_when necessary_.
That being said, the first change was the renaming of pid to procpid in
pg_stat_activity.
However, I contend that was more because someone felt that it was more to
make the column names
consistent across catalogs, rather than necessity. Care should be taken to
consider the need and
effect of changing EXISTING system columns. It may have been a mistake in
originally naming it,
but it was certainly working and not causing any problems at the time.
Just my personal opinion.

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!