Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 08BFB4759B5
	for <pgsql-sql@postgresql.org>; Sat, 11 May 2002 11:33:32 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 14F29475996
	for <pgsql-sql@postgresql.org>; Sat, 11 May 2002 11:33:31 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4BFWxW12914;
	Sat, 11 May 2002 11:32:59 -0400 (EDT)
To: Jan Wieck <janwieck@yahoo.com>
Cc: "Rajesh Kumar Mallah." <mallah@trade-india.com>, pgsql-sql@postgresql.org
Subject: Re: pg_stat_get_backend_pid seems to be listing non existant 
In-reply-to: <200205101048.g4AAmAK03594@saturn.janwieck.net> 
References: <200205101048.g4AAmAK03594@saturn.janwieck.net>
Comments: In-reply-to Jan Wieck <janwieck@yahoo.com>
	message dated "Fri, 10 May 2002 06:48:09 -0400"
Date: Sat, 11 May 2002 11:32:59 -0400
Message-ID: <12911.1021131179@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200205/108
X-Sequence-Number: 7744

Jan Wieck <janwieck@yahoo.com> writes:
>     And,  before  you  discover this one: The backends send their
>     statistic collection information via UDP packets. In the case
>     of heavy database load, some of these packets can get lost so
>     that the statistics will not be  100%  accurate.

Recently the SourceForge DBAs got quite confused by this: under load,
the pg_stats_activity view would show query-in-progress entries for
backends that were not only not busy any more, but actually had
terminated long since.  It took awhile to realize that this was pgstats
operating as designed and not a symptom of serious problems.

Although it's okay for pg_stats to lag the true state of affairs by
some amount of time, it's not good for a view that claims to be
current state to be wrong for indefinitely long periods.  Would it
be possible to improve the reliability of transmission of backend-quit
messages somehow?

One idea that comes to mind is for pgstats to look through the shared
memory PROC list occasionally to see if its idea of active processes
still matches reality.  Both idle and dead processes could be reliably
detected that way; also, we could detect busy (or at least
in-a-transaction) processes and change their viewable state to
"<unknown query>" if we hadn't gotten any query text from them.
Interestingly, this approach would allow a somewhat useful
pg_stats_activity view to be maintained even without *any* messages
transmitted by backends.

			regards, tom lane