Hi all,

Was looking at how to easily check which replication events are 
outstanding, in a way that works for multiple slaves.

Came up with this bit of SQL (using the _slony_example schema):

SELECT st.set_id AS set,
     ev.ev_seqno AS event_num,
     pa.pa_server AS source_node,
     pa.pa_client AS target_node,
     now()::timestamp - ev.ev_timestamp AS lag
FROM _slony_example.sl_set st
     LEFT JOIN _slony_example.sl_event ev ON st.set_origin = ev.ev_origin
     LEFT OUTER JOIN _slony_example.sl_path pa ON ev.ev_origin = 
pa.pa_server
     LEFT OUTER JOIN _slony_example.sl_confirm cf ON ev.ev_seqno = 
cf.con_seqno
WHERE cf.con_seqno IS NULL
ORDER BY set, event_num, source_node, target_node;

Seems to do the job.  Thought it might be useful to add to the main 
Slony view(s), so created a patch against the latest CVS if its wanted 
Called the view "sl_outstandingevents".

Regards and best wishes,

Justin Clift


***********

Index: slony1_base.sql
===================================================================
RCS file: 
/usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_base.sql,v
retrieving revision 1.13
diff -c -r1.13 slony1_base.sql
*** slony1_base.sql	19 May 2004 19:38:28 -0000	1.13
--- slony1_base.sql	28 Jul 2004 08:19:10 -0000
***************
*** 267,273 ****
   			"pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
   		where S.set_id = SQ.seq_set
   			and PGC.oid = SQ.seq_reloid and PGN.oid = PGC.relnamespace;
! 		

   -- ----------------------------------------------------------------------
   -- TABLE sl_log_1
--- 267,291 ----
   			"pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
   		where S.set_id = SQ.seq_set
   			and PGC.oid = SQ.seq_reloid and PGN.oid = PGC.relnamespace;
!
!
! -- ----------------------------------------------------------------------
! -- VIEW sl_outstandingevents
! -- ----------------------------------------------------------------------
!
! CREATE view @NAMESPACE at .sl_outstandingevents AS
! 	SELECT st.set_id AS set,
! 			ev.ev_seqno AS event_num,
! 			pa.pa_server AS source_node,
! 			pa.pa_client AS target_node,
! 			now()::timestamp - ev.ev_timestamp AS lag
! 	FROM @NAMESPACE at .sl_set st
! 			LEFT JOIN @NAMESPACE at .sl_event ev ON st.set_origin = ev.ev_origin
! 			LEFT OUTER JOIN @NAMESPACE at .sl_path pa ON ev.ev_origin = pa.pa_server
! 			LEFT OUTER JOIN @NAMESPACE at .sl_confirm cf ON ev.ev_seqno = cf.con_seqno
! 	WHERE cf.con_seqno IS NULL
! 	ORDER BY set, event_num, source_node, target_node;
!

   -- ----------------------------------------------------------------------
   -- TABLE sl_log_1