Nice job, eugene-khyst. Looks very comprehensive from an initial skim.
I've worked on something in the same space, with a focus on reliable but flexible synchronization to many consumers, where logical replication gets impractical.
I have a mind to do a proper writeup, but at least there is code at https://github.com/cognitedata/txid-syncing (MIT-licensed) and a presentation at https://vimeo.com/747697698
The README mentions …
> A long-running transaction in the same database will effectively "pause" all event handlers.
… as the approach is based on the xmin-horizon.
My linked code works with involving the MVCC snapshot's xip_list as well, to avoid this gotcha.
Also, note that when doing a logical restore of a database, you're working with different physical txids, which complicates recovery. (So my approach relies on offsetting the txid and making sure the offset is properly maintained)
I've worked on something in the same space, with a focus on reliable but flexible synchronization to many consumers, where logical replication gets impractical.
I have a mind to do a proper writeup, but at least there is code at https://github.com/cognitedata/txid-syncing (MIT-licensed) and a presentation at https://vimeo.com/747697698
The README mentions …
> A long-running transaction in the same database will effectively "pause" all event handlers.
… as the approach is based on the xmin-horizon.
My linked code works with involving the MVCC snapshot's xip_list as well, to avoid this gotcha.
Also, note that when doing a logical restore of a database, you're working with different physical txids, which complicates recovery. (So my approach relies on offsetting the txid and making sure the offset is properly maintained)