Re: BUG #10123: Weird entries in pg_stat_activity

On Fri, Apr 25, 2014 at 4:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> maxim(dot)boguk(at)gmail(dot)com writes:
> > During debug of the weird nagios database alerts about long transactions
> I
> > found that sometime pg_stat_activity contains very weird entries with
> > xact_start NOT NULL and state='idle'...
> > ...
> > What's more: i tested that problem on another databases - and found they
> > have same effect (so it doesn't look like a problem local to that single
> > exact database).
>
> So can you provide a self-contained test case for this? I tried to
> reproduce it here without much success, and I don't see a code path
> other than PREPARE TRANSACTION that could lead to this.
>
> regards, tom lane
>

I going to work on test case over weekend.
Initial quick and dirty attempts to reproduce outside of complicated
production environments provided no results yet.

What I almost sure now that reproducing of the bug requires a mix of very
active connections to the database (with a lot of requests per second) with
semi-idle connections.
And that transient problem affects only these semi-idle connections.

I will provide an update when I get more information.

Kind Regards,
Maksym