Re: BUG #10123: Weird entries in pg_stat_activity

On 2014-04-24 12:26:09 +0200, Magnus Hagander wrote:
> On Thu, Apr 24, 2014 at 12:18 PM, <maxim.boguk@gmail.com> wrote:
> >
]----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > ts               | 2014-04-24 13:49:03.498354+04
> > tx_age           | 00:04:01.283106
> > change_age       | 00:04:01.279373
> > datid            | 16385
> > datname          | *******
> > pid              | 685
> > usesysid         | 16384
> > usename          | *******
> > application_name |
> > client_addr      | 127.0.0.1
> > client_hostname  |
> > client_port      | 37461
> > backend_start    | 2014-04-24 13:30:03.275711+04
> > xact_start       | 2014-04-24 13:45:02.215248+04
> > query_start      | 2014-04-24 13:45:02.215248+04
> > state_change     | 2014-04-24 13:45:02.218981+04
> > waiting          | f
> > state            | idle
> > query            |
> >                  |                     SELECT cache.save_response(
> >                  |                      *****
> >                  |                     );

> This sounds like the expected behavior. When state=Idle, the query field
> shows the value of the *last* query that ran. See
> http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
> .
>
> Note that this was changed in 9.2, and at the same time the current_query
> field was renamed to query to highlight this change.
>
> The fact that xact_start shows something far in the past just means that
> there is an open transaction that has been open since then.

I don't think that's true. In that case the state should have been idle
in transaction, no?

I think there's a pgstat_report_xact_timestamp(0) missing from
xact.c:PrepareTransaction(). There's no point in reporting a prepared
xact's starttime in a backend that's not associated with it anymore.

Maxim, are you using prepared transactions?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services