heroku/heroku — pull request #948 by deafbybeheading (2013-11-18 17:00:23+00:00)
Title: Friendlier, more useful pg:ps output

Postgres considers connections in `pg_stat_activity` to be in one of three different states: actively running a query, idle, or idle but with an open transaction. `pg:ps` shows the status of non-idle connections, but due to the changes in `pg_stat_activity` in Postgres 9.2, the current output is somewhat misleading.

First of all, we use the `query_start` column, which is the time the current (or last, when idle in transaction) query started, but `xact_start` is generally more likely to correlate to any issues the user may be investigating. E.g., if you leave a transaction open for 6 hours and then run a `SELECT 1` on it without committing, the `query_start` will be the time of that last query, but it's the 6 hour window (as will be clear through `xact_start`) that's actually the thing to be concerned about.

Second, 9.2 did a lot of great work to improve the semantics of `pg_stat_activity`: the query status is now displayed out-of-band in a separate column. Unfortunately, we hide that column, so it's really confusing when a 9.2 query is not actually running any longer, but its transaction is still open.

A 9.2 before the patch:

```console
$ heroku pg:ps maroon --app db-corral
pid | source | running_for | waiting | query
-------+--------+-----------------+---------+-----------
25116 | psql | 00:00:06.163768 | f | select 1;
(1 row)
```

A 9.2 after the patch:

```console
$ bin/heroku pg:ps maroon --app db-corral
pid | state | source | running_for | waiting | query
-------+---------------------+--------+-----------------+---------+-----------
25116 | idle in transaction | psql | 00:00:45.137881 | f | select 1;
(1 row)
```

A 9.1 both before and after the patch:

```console
$ bin/heroku pg:ps rose --app db-corral
procpid | source | running_for | waiting | query
---------+--------+-----------------+---------+-----------------------
19731 | psql | 00:00:12.350129 | f | in transaction
(1 row)
```

I'm not sold on these semantics--I think there's a more fundamental distinction between running queries and idle transactions that we're missing here--but I believe this is better than what's currently there.