On Wednesday, May 8, 2024 5:21 PM Bertrand Drouvot <bertranddrouvot...@gmail.com> wrote: > A few comments: Thanks for the comments!
> 2 === > > +test_sub=# SELECT > + array_agg(slotname) AS slots > + FROM > + (( > + SELECT r.srsubid AS subid, CONCAT('pg_', srsubid, '_sync_', > srrelid, '_', ctl.system_identifier) AS slotname > + FROM pg_control_system() ctl, pg_subscription_rel r, > pg_subscription s > + WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND > s.subfailover > + ) UNION ( > > I guess this format comes from ReplicationSlotNameForTablesync(). What > about creating a SQL callable function on top of it and make use of it in the > query above? (that would ensure to keep the doc up to date even if the format > changes in ReplicationSlotNameForTablesync()). We could add a new function as suggested but I think it's not the right time(beta1) to add this function because new function will bring catversion bump which I think may not be worth at this stage. I think we can consider this after releasing and maybe gather more use cases for the new function you suggested. > > 3 === > > +test_sub=# SELECT > + MAX(remote_lsn) AS remote_lsn_on_subscriber > + FROM > + (( > + SELECT (CASE WHEN r.srsubstate = 'f' THEN > pg_replication_origin_progress(CONCAT('pg_', r.srsubid, '_', r.srrelid), > false) > + WHEN r.srsubstate IN ('s', 'r') THEN r.srsublsn > END) AS remote_lsn > + FROM pg_subscription_rel r, pg_subscription s > + WHERE r.srsubstate IN ('f', 's', 'r') AND s.oid = r.srsubid > AND > s.subfailover > + ) UNION ( > + SELECT pg_replication_origin_progress(CONCAT('pg_', > s.oid), false) AS remote_lsn > + FROM pg_subscription s > + WHERE s.subfailover > + )); > > What about adding a join to pg_replication_origin to get rid of the > "hardcoded" > format "CONCAT('pg_', r.srsubid, '_', r.srrelid)" and "CONCAT('pg_', s.oid)"? I tried a bit, but it doesn't seem feasible to get the relationship between subscription and origin by querying pg_subscription and pg_replication_origin. Best Regards, Hou zj