Re: IDLE in transaction introspection

On Fri, Nov 4, 2011 at 10:34 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> I guess with the changes that showed different thing like fastpath,
>> that makes sense. But if we just mapped the states that are there
>> today straight off, is there any case where waiting can be true, when
>> we're either idle or idle in transaction? I think not..
>
> I'm not totally sure about that.  I know that it's possible to see "idle
> waiting" in the ps display, but that comes from getting blocked in parse
> analysis before the command type has been determined.  The
> pg_stat_activity string is set a bit earlier, so *maybe* it's impossible
> there.  Still, why wire such an assumption into the structure of the
> view?  Robert's point about sinval catchup is another good one, though
> I don't remember what that does to the pg_stat_activity display.
>
> BTW, a quick grep shows that there are four not two special values of
> the activity string today:
>
> src/backend/tcop/postgres.c: 3792:                 pgstat_report_activity("<IDLE> in transaction (aborted)");
> src/backend/tcop/postgres.c: 3797:                 pgstat_report_activity("<IDLE> in transaction");
> src/backend/tcop/postgres.c: 3805:                 pgstat_report_activity("<IDLE>");
> src/backend/tcop/postgres.c: 3925:                 pgstat_report_activity("<FASTPATH> function call");
>
> It's also worth considering whether the "autovacuum:" that's prepended
> by autovac_report_activity() ought to be folded into the state instead
> of continuing to put something that's not valid SQL into the query
> string.
>

Well, it would be interesting to see rows for autovacuum or
replication processes showing up in pg_stat_activity with a
corresponding state (autovacuum, walreciever?) and the "query" field
showing what they are working on, at the risk that we'd need to build
more complex parsing into the various monitoring scripts, but I guess
it's no worse than before (and I guess probably easier on some level).

Robert Treat
play: xzilla.net
work: omniti.com