Subject: Re: row filtering for logical replication
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.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>
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>
 <db552f21-b9b7-4631-1ea9-03a91379cc87@enterprisedb.com>
 <CAFiTN-vcY1DzqpnCKWEeees22zcfeoj6_oiO9eAJjgPBoRR-1g@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <7022de8a-0ba0-9af3-3be6-76ab5f7fbe39@enterprisedb.com>
Date: Wed, 14 Jul 2021 16:57:24 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFiTN-vcY1DzqpnCKWEeees22zcfeoj6_oiO9eAJjgPBoRR-1g@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7022de8a-0ba0-9af3-3be6-76ab5f7fbe39%40enterprisedb.com>
Precedence: bulk


On 7/14/21 4:48 PM, Dilip Kumar wrote:
> On Wed, Jul 14, 2021 at 8:04 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
> 
>> Perhaps the best way forward is to stick to the approach that INSERT
>> uses new, DELETE uses old and UPDATE works as DELETE+INSERT (probably),
>> and leave anything fancier (like being able to reference both versions
>> of the row) for a future patch.
> 
> If UPDATE works as DELETE+ INSERT, does that mean both the OLD row and
> the NEW row should satisfy the filter, then only it will be sent?
> That means if we insert a row that is not satisfying the condition
> (which is not sent to the subscriber) and later if we update that row
> and change the values such that the modified value matches the filter
> then we will not send it because only the NEW row is satisfying the
> condition but OLD row doesn't.  I am just trying to understand your
> idea.  Or you are saying that in this case, we will not send anything
> for the OLD row as it was not satisfying the condition but the
> modified row will be sent as an INSERT operation because this is
> satisfying the condition?
> 

Good questions. I'm not sure, I probably have not thought it through.

So yeah, I think we should probably stick to the principle that what we 
send needs to match the filter condition, which applied to this case 
would mean we should be looking at the new row version.

The more elaborate scenarios can be added later by a patch allowing to 
explicitly reference the old/new row versions.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company