CODEWITHSUNDEEP
debuggingpostgresqltransactionslocking
Nicolas
Nicolas

Reputation: 2321

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

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.

Upvotes: 2

Views: 605

Answers (2)

Greg Smith
Greg Smith

Reputation: 18136

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.

Upvotes: 2

user80168
user80168

Reputation:

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.

Upvotes: 2

Related Questions