Which Java thread is responsible for a PostgreSQL database process?

Question

Given a JVM connecting to a PostgreSQL database (on Linux), is there a way to find out which thread in Java is responsible for a process in the database?

NB: I'm debugging a resource-leak bug, which shows up as some idle-in-transaction processes in the database. It would be useful to match these up with the thread responsible, as the thread name will narrow down the search.

Surely there's a connection to the database, which might potentially be used by multiple threads (e.g. via connection pooling). — Jon Skeet
– Jon Skeet, Commented Apr 4, 2012 at 23:24
In many applications, connections will be explicitly assigned to a thread. Connections should not generally be used by multiple threads at the same time (the implementation may make this possible, but many do not). Sure, it can be more efficient to have a connection pool that reuses threads appropriately, but programs in the wild often do not. Sometimes it's just easier to have assigned connections. Why complicate the question? — Edmund
– Edmund, Commented Apr 5, 2012 at 0:29

kgrittn · Accepted Answer · 2012-04-04 23:48:57Z

5

You could set application_name (with the SET statement) to something that you can match up with the Java thread name.

answered Apr 4, 2012 at 23:48

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edmund · Accepted Answer · 2012-04-06 10:44:59Z

5

A useful trick is to send a SIGQUIT signal to the Java process, e.g.

kill -QUIT your_process_id

Which will not kill it, but will tell it to print a thread dump. This will list all the threads, with a backtrace of what they are currently doing. Unfortunately this won't usually contain something as obvious as "conn.setAutoCommit(false)", but it will tell you where each thread is.

You can then find those points in the code and attempt to trace back to see if any are preceeded by transaction begins that are unmatched by commits/rollbacks.

Another thing that is useful when the application is being written is to have a common place where connections are created and controlled (i.e. yet another abstraction layer!). This becomes a good place to put statements for logging when connections are created and assigned to threads, and when a transaction is begun on one of them.

edited Apr 6, 2012 at 10:44

answered Apr 5, 2012 at 0:40

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

1 Comment

Daniel Winterstein Over a year ago

Re logging & an abstraction layer, the BoneCP connection pool comes with some useful debugging support, including config.setCloseConnectionWatch() -- which creates a helper thread to watch for un-released connections (comes with a performance warning).

Collectives™ on Stack Overflow

Which Java thread is responsible for a PostgreSQL database process?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related