Re: BUG #10123: Weird entries in pg_stat_activity

On Thu, Apr 24, 2014 at 8:44 PM, Andres Freund <andres@2ndquadrant.com>wrot=
e:

> 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=3DIdle, the query fi=
eld
> > shows the value of the *last* query that ran. See
> >
> http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-A=
CTIVITY-VIEW
> > .
> >
> > Note that this was changed in 9.2, and at the same time the current_que=
ry
> > field was renamed to query to highlight this change.
> >
> > The fact that xact_start shows something far in the past just means tha=
t
> > 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=3D# 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=3D'idle' is correct).


Kind Regards,
Maksym