To: Cosimo Streppone <cosimo@streppone.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: pgtop, display PostgreSQL processes in `top' style 
In-reply-to: <42740256.4090200@streppone.it> 
References: <42740256.4090200@streppone.it>
Comments: In-reply-to Cosimo Streppone <cosimo@streppone.it>
	message dated "Sun, 01 May 2005 00:10:30 +0200"
Date: Sun, 01 May 2005 23:04:49 -0400
Message-ID: <9988.1115003089@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Cosimo Streppone <cosimo@streppone.it> writes:
> 1) is it possible to know Pg backend uptime with
>     SQL queries? Or must I look at postmaster.pid file?
>     or even something else?

I suppose you mean postmaster uptime, not the lifespan of your own
connection.  We don't track that at the moment, but IIRC there is
a patch in the queue to provide a function that returns this for 8.1.
For now you could perhaps look at "ps" output to see how long the
postmaster process has been running, but that's surely ugly and fraught
with pitfalls ...

Interesting thought here: should we track the postmaster process uptime,
or the time since the last database restart?  Not the same at all.
You could argue that from a reliability standpoint the latter is the
interesting number.

> 2) how can I know the ip addresses/hostnames of clients
>     connecting to server?

You can't (again, unless you want to parse "ps" output).  But I think
someone has submitted a patch to add such columns to the
pg_stat_activity view.  We need to figure out whether the visibility
of these columns needs to be restricted for security, but pending that
discussion it'll probably be in 8.1 in some form.

> 3) Is there a way to know the number of queries performed
>     against Pg backend (by each client)? Can I distinguish
>     between selects / updates / inserts / copy / ... ?

Nope, and nope.  We could perhaps teach the stats collector to count
querystrings it's received from each backend, which'd answer the first
one to a reasonable extent.  I'm unconvinced that we should expend the
overhead to be able to do the second.

			regards, tom lane