Re: system views for walsender activity

On Tue, 2011-01-04 at 15:51 +0900, Itagaki Takahiro wrote:
> On Tue, Dec 28, 2010 at 22:17, Magnus Hagander <magnus(at)hagander(dot)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;

Just seen you started working on this again. Very good.

I enclose some snippets of code I was working on, which I am removing
from my patch in favour of your work as a separate commit.

The way I coded it was a new SRF that joins to the existing
pg_stat_activity. So no initdb required, and this can also easily be
included as an external module for 9.0.

Please notice also that my coding of the new SRF does not have the O^2
issue you mention, which I was keen to avoid.

The sent pointer is needed whether or not we have sync rep. We should
also include application name, since the user may set that in the
standby for all the same reasons it is set elsewhere.

Small point: please lets not call this pg_stat_walsender?
pg_stat_replication_sent and pg_stat_replication_received would be
easier for normal humans to understand.

I would very much appreciate it if one of you could complete something
here and commit in the next few days. That would then allow me to extend
the view with sync rep specific info for monitoring and patch testing.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services