Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>
Cc: 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>
 <CAFiTN-t37Uo1NP9PR68n619S3Qkbareahu_h3h+Kp=7cKzs85w@mail.gmail.com>
 <CAA4eK1LB7-THsVzB7h-w9gSf7Cym3SZ++6s5CKNL-OfNfQmARQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <cf6101dd-01d5-0ae2-0b32-ee2fc31b0ea5@enterprisedb.com>
Date: Fri, 16 Jul 2021 10:45:59 +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: 
 <CAA4eK1LB7-THsVzB7h-w9gSf7Cym3SZ++6s5CKNL-OfNfQmARQ@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/cf6101dd-01d5-0ae2-0b32-ee2fc31b0ea5%40enterprisedb.com>
Precedence: bulk


On 7/16/21 5:26 AM, Amit Kapila wrote:
> On Wed, Jul 14, 2021 at 4:30 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>
>> On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
>> <tomas.vondra@enterprisedb.com> wrote:
>>>
>>> Is there some reasonable rule which of the old/new tuples (or both) to
>>> use for the WHERE condition? Or maybe it'd be handy to allow referencing
>>> OLD/NEW as in triggers?
>>
>> I think for insert we are only allowing those rows to replicate which
>> are matching filter conditions, so if we updating any row then also we
>> should maintain that sanity right? That means at least on the NEW rows
>> we should apply the filter, IMHO.  Said that, now if there is any row
>> inserted which were satisfying the filter and replicated, if we update
>> it with the new value which is not satisfying the filter then it will
>> not be replicated,  I think that makes sense because if an insert is
>> not sending any row to a replica which is not satisfying the filter
>> then why update has to do that, right?
>>
> 
> There is another theory in this regard which is what if the old row
> (created by the previous insert) is not sent to the subscriber as that
> didn't match the filter but after the update, we decide to send it
> because the updated row (new row) matches the filter condition. In
> this case, I think it will generate an update conflict on the
> subscriber as the old row won't be present. As of now, we just skip
> the update but in the future, we might have some conflict handling
> there.

Right.

> If this is true then even if the new row matches the filter,
> there is no guarantee that it will be applied on the subscriber-side
> unless the old row also matches the filter. Sure, there could be a > case where the user might have changed the filter between insert and
> update but maybe we can have a separate way to deal with such cases if
> required like providing some provision where the user can specify
> whether it would like to match old/new row in updates?
> 

I think the best we can do for now is to document this. AFAICS it can't 
be solved without a conflict resolution that would turn the UPDATE to 
INSERT. And that would require REPLICA IDENTITY FULL, otherwise the 
UPDATE would not have data for all the columns.


regards

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