Associating Postgres Connections with Java code

Question

I'm trying to track down leaky connections. (In particular, I'm noticing connections staying open past the closure of the last DataSource, using C3P0 for connection pooling. I suspect a bug but want to ensure I'm not just being stupid first.)

Is there a good way to associate open connections from the Postgres view

postgres=# select * from pg_stat_activity;

datid | datname  | procpid | usesysid | usename  |          current_query          | waiting |          xact_start          |         query_start          |         backend_start         | client_addr | client_port 
11564 | postgres |   95709 |       10 | postgres | select * from pg_stat_activity; | f       | 2010-07-25 14:20:24.97529-07 | 2010-07-25 14:20:24.97529-07 | 2010-07-25 14:20:19.066576-07 |             |          -1

(1 row)

with the location they were opened in the Java / JDBC code? (Or perhaps some unique string, etc) Seems like this ought to be a useful thing to have!

Answer 1

This is impossible to check from in the PostgreSQL side. It knows nothing about the Java code. The caller has just to pass-in the username, password and DB name. Nothing more. It's however possible to check it from in the Java side. Tools like log4jdbc may be helpful in this.

Answer 2

Postgresql 9 supports an application_name setting that can be read in this way, but there is nothing similar in the server for versions before that.

With JBoss, we had a connection guard system that would throw and catch an exception internally whenever a connection was opened, in order to capture the backtrace, and then store that along with the connection. Then if the connection wasn't closed at the end of the transaction, that backtrace was logged with a warning. It's possible, I guess, to adapt that strategy into a generic datasource-decorator layer and periodically sweep the tracked connections for ones that haven't been used in 10 minutes (or sth greater than the expected lifetime) but not closed.