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(dot)boguk(at)gmail(dot)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