Обсуждение: SELECT .. FOR UPDATE: find out who locked a row

Check out here: https://wiki.postgresql.org/wiki/Lock_Monitoring



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Greetings,

* Enrico Thierbach (eno@open-lab.org) wrote:
> I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement a
> queueing system.
>
> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns the
> lock.

Sure, you can attempt to lock the record and then run pg_blocking_pids()
(in another session) against the pid which is trying to acquire the
lock.

Session #1:

Connect
SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
... gets back some id X
... waits

Session #2:

Connect
SELECT pg_backend_pid(); -- save this for the 3rd session
SELECT * FROM queue WHERE id = X FOR UPDATE;
... get blocked waiting for #1
... waits

Session #3:

SELECT pg_blocking_pids(SESSION_2_PID);
-- returns PID of Session #1

Obviously there's race conditions and whatnot (what happens if session
#1 releases the lock?), but that should work to figure out who is
blocking who.

If you're on a version of PG without pg_blocking_pids then you can look
in the pg_locks view, though that's a bit more annoying to decipher.

Thanks!

Stephen

Greetnigs,

* Enrico Thierbach (eno@open-lab.org) 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.

What I gave you would work, or you could use the pgrowlocks extension:

https://www.postgresql.org/docs/current/static/pgrowlocks.html

Using pgrowlocks will be slow if you have a lot of records in the table
though, hence the other approach I mentioned since it sounds like you
know the ID that you're interested in.

Thanks!

Stephen

Greetings Melvin,

* Melvin Davidson (melvin6925@gmail.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

Greetings Melvin,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> On Thu, Mar 15, 2018 at 10:14 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > 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.
>
> 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.

As mentioned earlier, care is taken when considering the need and effect
of changing existing system columns, but it can, and will, happen, and
therefore applications should be designed to cope with such changes
gracefully when they happen and tested thoroughly on each new major
version of PostgreSQL.

Thanks!

Stephen

Melvin Davidson <melvin6925@gmail.com> writes:
> 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.

Please read all of
https://www.postgresql.org/message-id/flat/201106091554.p59Fso314146%40momjian.us
where this was discussed to death (and rejected), and then read all of
https://www.postgresql.org/message-id/flat/CAKq0gvK8PzMWPv19_o7CGg8ZQ0G%2BUuAWor5RrAg0SOmWTqqLwg%40mail.gmail.com
which is the thread in which the change was agreed to after all
(on the grounds that we were breaking backwards compatibility of
the view anyway with respect to other, more important, columns).

If you still feel that we make incompatible changes without adequate
consideration, that's your right, but you might want to consider
speaking up in some reasonable time frame, not six years later.
This could have been objected to as late as 9.2 beta, so it's not
like you need to be drinking from the pgsql-hackers firehose continually
in order to weigh in.  But 9.2 is not just released, it's EOL, so it's
really kinda late to be objecting.

            regards, tom lane

PG10

Is there an operator to determine if a box and circle intersect? 
I can only see box && box and can use centre+rad and distance to calculate
circle:circle.

Thanks.

Am 16.03.2018 um 11:00 schrieb Martin Moore:
> PG10
>
> Is there an operator to determine if a box and circle intersect?
> I can only see box && box and can use centre+rad and distance to calculate
> circle:circle.
>
> Thanks.
>
>
please don't hijack other mail-threads by answering & changing the 
subject, your mail still contains references to "SELECT .. FOR UPDATE: 
find out who locked a row"
Create a new mail for a new thread!


to your question: consider PostGIS and it's functions st_intersects()

Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Cheers - I'd tried postGIS on 9.6 but had install issues. Installed first time on 10 :)


-----Original Message-----
From: Andreas Kretschmer [mailto:andreas@a-kretschmer.de]
Sent: 16 March, 2018 11:46 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Circle and box intersect



Am 16.03.2018 um 11:00 schrieb Martin Moore:
> PG10
>
> Is there an operator to determine if a box and circle intersect?
> I can only see box && box and can use centre+rad and distance to
> calculate circle:circle.
>
> Thanks.
>
>
please don't hijack other mail-threads by answering & changing the subject, your mail still contains references to
"SELECT.. FOR UPDATE:  
find out who locked a row"
Create a new mail for a new thread!


to your question: consider PostGIS and it's functions st_intersects()

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Greetings,

Please don't top-post.

* Melvin Davidson (melvin6925@gmail.com) wrote:
> this whole discussion started because Enrico did not originally specify the
> PostgreSQL version he was working with. So after he did advise it was for
> 9.6, I felt it necessary to explain to him why a certain section of my
> query was commented out and that it would also work for 10. I have
> previously made it a policy to request ops include the PostgreSQL version
> and O/S when submitting to this list, but I was berated by others for
> always requesting ops to provide that extremely difficult information to
> obtain.

There's a difference between saying that a particular query is intended
for certain versions and to ask for what version while providing useful
information, and just immediately replying to every email which doesn't
specify it asking for what the version and OS is.  The former is being
helpful and specific while soliciting for additional information, while
the latter tends to just create noise on the list, particularly when the
question isn't ultimately relevant.

Note that the above comments are entirely generic- I'm not aware of the
specific emails which you're referring to or why it was suggested that
they weren't helpful.  Specific concerns regarding list usage should
really be addressed to the list moderators and not individuals taking
action on their own.

> I also felt it important that I express my opinion that the changes needed
> were caused by what I felt was cosmetic and unnecessary changes to the
> catalog. There is an old saying "If it ain't broke, don't fix it" and that
> certainly applies here.

There was a great deal of discussion and consideration for the changes
and specific reasons why they were made.

> Now, as to your request for me to read the thread in the url's you
> suggested, I did read most of the content. I note that the entire
> discussion was amongst
> PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>, So at no time
> was the generic population of PostgreSQL users and DBA's involved.

That version of PostgreSQL, as with all of them, went through a beta
period where we specifically ask for feedback from users.  Serious
concerns raised during that time period do result in changes being made
prior to release.

> Therefore, said population, myself included, had no foreknowledge of the
> intended changes which is the cause of the problem. Therefore your
> statement "you might want to consider speaking up in some reasonable time
> frame, not six years later" is abrasive at best, since I, and others, only
> found out about it after the fact. Not to mention, even if I did complain
> earlier, I seriously doubt the changes could or would be reversed.

If you're interested in having a say in what will be included in the
next version of PostgreSQL then I strongly encourage you, and all users,
to work with the beta packages that are put out, every year, for people
to test with.

> At this point I have said all I have to say and will discuss it no further.
> I can only strongly recommend that in the future, proposed changes to
> system catalogs that could adversely affect existing scripts and
> applications be sent to the generic PostgreSQL population (IE:
> pgsql-general@lists.postgresql.org) for comment BEFORE said changes are
> implemented.

Beta releases are announced through the pgsql-announce mailing list,
which is a list that has a great deal less traffic than -general and one
which I'd suggest all users subscribe to, to be aware of changes which
are likely to be in the next release and to test to see if there are any
serious issues, and also to update their applications and queries in
advance of changes being released.

Thanks!

Stephen

Enrico,

* Enrico Thierbach (eno@open-lab.org) wrote:
> >*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?

Evidently my second email got lost somewhere along the way- what you're
looking for is an extension called 'pgrowlocks':

https://www.postgresql.org/docs/10/static/pgrowlocks.html

My prior email on that subject is here:

https://www.postgresql.org/message-id/20180315220512.GV2416%40tamriel.snowman.net

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

You can write it into the database, of course, but you can't read that
information out by some other process.  Depending on what you're doing,
you can use RAISE NOTICE to send messages back to the connected client
from within a stored procedure or similar.

Thanks!

Stephen

Thanks Steven,

> Evidently my second email got lost somewhere along the way- what
> you're
> looking for is an extension called 'pgrowlocks':
>
> https://www.postgresql.org/docs/10/static/pgrowlocks.html
>
> My prior email on that subject is here:
>
> https://www.postgresql.org/message-id/20180315220512.GV2416%40tamriel.snowman.net
>

I’ll look into that as soon after next week.

Best,
/eno

--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel

> ​You probably considered this but the queuing mechanism I use 
> doesn't hold
> locks on records during processing.  Workers claim tasks by locking 
> them,
> setting a claimed flag of some sort, the releasing the lock (including
> worker identity if desired) - repeating the general procedure once
> completed.
>
> My volume is such that the bloat the extra update causes is not 
> meaningful
> and is easily handled by (auto-)vacuum​.
>
> David J.

Hi David,

well, I though about it and then put it to rest initially, since I liked 
the idea that with a running job kept “inside” the transaction I 
would never have zombie entries there: if somehow the network connection 
gets lost for the client machines the database would just rollback the 
transaction, the job would revert to its “ready-to-run” state, and 
the next worker would pick it up.

However, I will probably reconsider this, because it has quite some 
advantages; setting a “processing” state, let alone keeping a worker 
identitiy next to the job seems much more straightforward.

So how do you solve the “zombie” situation?

Best,
Enrico