On Wed, Jul 14, 2021 at 10:50 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > > I think apart from the above, it might be good if we can find what > some other databases does in this regard? >
I did a bit of investigation in the case of Oracle Database and SQL Server. (purely from my interpretation of available documentation; I did not actually use the replication software) For Oracle (GoldenGate), it appears that it provides the ability for filters to reference both OLD and NEW rows in replication of UPDATEs: "For update operations, it can be advantageous to retrieve the before values of source columns: the values before the update occurred. These values are stored in the trail and can be used in filters and column mappings" It provides @BEFORE and @AFTER functions for this. For SQL Server, the available replication models seem quite different to that in PostgreSQL, and not all seem to support row filtering. For "snapshot replication", it seems that it effectively supports filtering rows on the NEW values. It seems that the snapshot is taken at a transactional boundary and rows included according to any filtering, and is then replicated. So to include the result of a particular UPDATE in the replication, the replication row filtering would effectively be done on the result (NEW) rows. Another type of replication that supports row filtering is "merge replication", which again seems to be effectively based on NEW rows: "For merge replication to process a row, the data in the row must satisfy the row filter, and it must have changed since the last synchronization" It's not clear to me if there is ANY way to filter on the OLD row values by using some option. If anybody has experience with the replication software for these other databases and I've interpreted the documentation for these incorrectly, please let me know. Regards, Greg Nancarrow Fujitsu Australia
