Re: system views for walsender activity

On Tue, Dec 28, 2010 at 22:17, Magnus Hagander <magnus@hagander.net> wrote:
>>> We definitely need the very basic level for 9.1, and we can always
>>> improve on it later :-)
>
>>> pg_stat_walsender. It would then only need the columns for procpid,
>>> usesysid, usename, client_addr, client_port, and the WALsender
>>> specific fields.
> Yeah, agreed. backend_start is probably the best one

Here are patches for pg_stat_walsender.
I split the feature into two pieces:

* get_host_and_port.patch
It separates host and port formatter as a subroutine from pg_stat_activity.
In addition, make pg_stat_get_backend_client_addr/port() functions to
use the subroutine to reduce duplicated codes.

* pg_stat_walsender.patch
It adds pg_stat_walsender system view. It has subset columns of
pg_stat_activity and only one additional WAL sender specific information
via WALSndStatus(). I named the column "sending location" because
standby servers might not have received the WAL record; if we had
synchronous replication, a new "sent location" wold be added.
But the naming is still an open question. Comments welcome.

There is O(max_wal_senders^2) complexity in the view, But I think
it is not so serious problem because we can expect max_wal_senders
is 10 or so at most.

CREATE VIEW pg_stat_walsender AS
    SELECT
            S.procpid,
            S.usesysid,
            U.rolname AS usename,
            S.client_addr,
            S.client_port,
            S.backend_start,
            S.xlog_sending
    FROM pg_stat_get_walsender(NULL) AS S, pg_authid U
    WHERE S.usesysid = U.oid;

--
Itagaki Takahiro