1

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?

djsoteric
  • 188
  • 1
  • 10
  • you can check for connections that have uncommitted changes by looking at `pg_stat_activity` for sessions that are `idle in transaction` –  Dec 16 '19 at 22:01
  • I mentioned that I have tried this already. There is no such data being returned in that query. All of the "state" columns are NULL. – djsoteric Dec 16 '19 at 22:02
  • 1
    Then you have no open transactions and your problem lies elsewhere. What exactly was the query you were using? –  Dec 16 '19 at 22:04
  • select * from pg_stat_activity and filtering on the state column. When I run this query without state, I can see a few but they are all NULL instead of idle. – djsoteric Dec 16 '19 at 22:06
  • 1
    The `state` column is never null except for background processes –  Dec 16 '19 at 22:07
  • Not sure what I'm supposed to make of that. I have existing java processes which start up and say that I have a PK which does not exist in any capacity that I can see. This happens every time the java app begins a transaction but doesn't commit it or roll it back. The data is not actually stored in the DB as persisted data because I'm able to go to the DB myself and add the same PK it thinks already exists. – djsoteric Dec 16 '19 at 23:58
  • You have to come up with sample code and data to reproduce this. Uncommitted data are stored in the tables, but as soon as the transaction that created them is gone, they are invisible for all practical purposes and will never cause a constraint violation. – Laurenz Albe Dec 17 '19 at 07:20
  • This is not an application I have written (I just know that it opens a connection at the start of the process and fails to close it when it is terminated unexpectedly) so I do not know where that is happening. All I know is that there is some weird persisted data because it goes away when I completely kill postgres volume data via docker. I just want to avoid that from happening. Is there nothing like sql profiler, etc. where I can simply view all connections? The transaction is not "gone" - that's the problem. – djsoteric Dec 17 '19 at 07:56
  • Are you viewing pg_stat_activity as a superuser? And what version? – jjanes Dec 17 '19 at 11:01
  • yes - I'm able to drop and create schemas, etc. – djsoteric Dec 17 '19 at 14:58

1 Answers1

0

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I guess that's what I'm trying to say but not properly wording. The transaction is persistent so it is still considering items that were added earlier. I just want to be able to kill any and all transactions. – djsoteric Dec 17 '19 at 14:57