Is there a way to get the list of SQL statements that were previously executed as part of a given transaction in PostgreSQL?

Question

I'm in a situation where I have many connections that are "IDLE in transaction". I spotted them myself. I could kill them, but that wouldn't prevent them from happening again. In fact, it happens regularily.

Is there a way to get the list of SQL statements that were previously executed as part of a given transaction?

If I could get that, it would be a hell lot easier to figure out the misbehaving client.

Answer 1

I had a similar question myself earlier, but I posted on serverfault.com as I think it would be more appropriate there:

http://serverfault.com/questions/20106/postgres-how-can-i-see-all-sql-statements-being-executed-by-the-database-server

Answer 2

Basically - you have to turn on all statements logging, with time of execution. best way to achieve it is to use log_min_duration_statement with value of 0, and using log_line_prefix such that is it will include information required to match lines coming form the same backend.

I generally use log_line_prefix = '%m %u@%d %p %r '.

Afterward you can write some tool to help you hunt idle-in-transaction, or you can use mine.

Answer 3

There is some work being done right now on the pgsql-hackers mailing list toward adding exactly this capability, under the title "display previous query string of idle-in-transaction". Where that looks to be going is that pg_stat_activity will have a new column named something like "last_query" that includes the info you want.

Until that's done and available in probably the next release, the suggestion from depesz is probably as good as you're going to get here--unless you want to start grabbing early patches working on this feature as they trickle out.