MIME-Version: 1.0
References: <202107132106.wvjgvjgcyezo@alvherre.pgsql>
 <48e13621-10f7-49ef-bcbe-ed530195efd2@www.fastmail.com>
 <CAA4eK1+0-R8Q8gVJGho3E6Lyor=1LqRtUmY7bCY668FFyKvcTg@mail.gmail.com>
 <d3592bf3-fb31-fd0e-592c-abfd3ae9d259@enterprisedb.com>
 <CAA4eK1LnHscyn6dfAUJ4o3Bnw12sAgEmxMUZOzc3CjRM9BOCkA@mail.gmail.com>
In-Reply-To: 
 <CAA4eK1LnHscyn6dfAUJ4o3Bnw12sAgEmxMUZOzc3CjRM9BOCkA@mail.gmail.com>
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Thu, 15 Jul 2021 21:50:46 +1000
Message-ID: 
 <CAJcOf-cNR4mUC53_Z12Fq9exVYN1ob4ofJSbuYRF7nEBia_jbw@mail.gmail.com>
Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Euler Taveira <euler@eulerto.com>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Peter Smith <smithpb2250@gmail.com>,
	Rahila Syed <rahilasyed90@gmail.com>,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
	=?UTF-8?B?w5ZuZGVyIEthbGFjxLE=?= <onderkalaci@gmail.com>,
	japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>,
	David Steele <david@pgmasters.net>, Craig Ringer <craig@2ndquadrant.com>,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Amit Langote <amitlangote09@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJcOf-cNR4mUC53_Z12Fq9exVYN1ob4ofJSbuYRF7nEBia_jbw%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 14, 2021 at 10:50 PM Amit Kapila <amit.kapila16@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