0

Sometimes we get a warning like this:

2019-... [15138] [Xc0BxiGn2g7j] LOG:  process 15138 still waiting for ShareLock on transaction 274489730 after 1000.107 ms
2019-... [15138] [Xc0BxiGn2g7j] DETAIL:  Process holding the lock: 14625. Wait queue: 15138.
2019-... [15138] [Xc0BxiGn2g7j] CONTEXT:  while deleting tuple (1,32) in relation "foobar"
2019-... [15138] [Xc0BxiGn2g7j] STATEMENT:  DELETE FROM "foobar" WHERE "foobar"."id" IN (2216750)

Unfortuantely the process id (in this case 14625) does not help much.

We set the application_name for all connections to PostgreSQL to a unique request-id.

Is there a way to let the above message print out the application name of the process which holds the connection?

Then it would make it much easier to investigate the root cause.

I updated the question, to show that the request-id of the blocked process is already visible to me. I am searching the request-id of process 14625.

2

Just include %p and %a in your log_line_prefix and set log_disconnections = on.

Then you get the application name and process ID logged whenever a session disconnects. That will enable you to find in the log which application name belongs to which session. This will work unless sessions are in the habit of changing their application_name.

For active connections, just examine pg_stat_activity.

1
  • This should work. We already include %a and %p in log_line_prefix. The hint to log_disconnections = on is new and good. Thank you!
    – guettli
    Nov 27 '19 at 13:36
0

We append request-id, module, action and few other parameters to every SQL thru SET application_name ''; This may not be the best approach, but this makes it easy to see the source of the query in logs, errors, analytics, etc.

3
  • It is correct what you say. We do that, too.
    – guettli
    Nov 28 '19 at 7:30
  • I think you are adding that per connection while I am suggesting to add it per sql STATEMENT. So the STATEMENT: in your warning would have the request-Id Nov 28 '19 at 7:36
  • I updated the question, to show that the request-id of the blocked process is already visible to me. I am searching the request-id of process 14625.
    – guettli
    Nov 28 '19 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.