Postgres - killing uncommitted transaction

Question

I have read of a few ways to do this (namely, querying the pg_stat_activity table to get the pid to kill), but I have no data coming back in those queries. In other DBMSs like MSSQL, I could at least see the uncommitted data and/or use profiler to see which connections are active. With postgres, I only know that this is the case because I am running a java application which occasionally closes unexpectedly which prevents the app from committing or rolling back its changes. When I try to run the app, I see errors for duplicate key data that isn't committed but is still active somehow. I have stopped my postgres instance but still ran into the problem once I opened it again. Is there another way to view anything which has a hanging connection to my db server and kill it?

Answer 1

The only way you get unique constraint errors from duplicate keys on uncommitted values is if both dups were inserted in that same transaction. Otherwise, the 2nd process to insert blocks until the first one either commits or rollsback.

If the process is bombing itself, then it is not surprising there is nothing to see in pg_stat_activity.