Re: BUG #10123: Weird entries in pg_stat_activity

On Thu, Apr 24, 2014 at 8:44 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> 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?
>
>
No, project isn't using prepared transactions. I absolutely sure.
postgres=# show max_prepared_transactions;
max_prepared_transactions
---------------------------
0

What strange - that exact pg_stat_activity entry stayed with xact_start
NULL for 4 minute after the last query for this pid finished, and after
that somehow got the live xact_start again (and in the next check iteration
after 0.1s xact_start become NULL again for that pid).
tx_age can vary, i seen anything between 1 second and 30 minutes... all
related to idle backends (so state='idle' is correct).

Kind Regards,
Maksym