Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Greg Nancarrow <gregn4422@gmail.com>, Euler Taveira <euler@eulerto.com>,
 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: 
 <CAJcOf-eciNBeRYMVVV1sbhjTQ-SXVnz1tdwkR3B24bAuBMpEZA@mail.gmail.com>
 <202107162135.m5ehijgcasjk@alvherre.pgsql>
 <CAA4eK1+79JVQ7dZqAE+0pc0f-AZn4J_F2X3nLdctGcAtcMp6Ew@mail.gmail.com>
 <CAFiTN-tZ6TT+hJt-MKwtumx62r1wnAVdDDuFyNNA==kLTsVgjQ@mail.gmail.com>
 <fb7a3099-1232-17d4-dd45-a2d42d780b1e@enterprisedb.com>
 <CAA4eK1+3=w++VOY5Z6VMeh1OLs9N+A3nwL+dUEuLgxGx2K-4Rg@mail.gmail.com>
 <ace1bc6e-f763-1791-2371-1d88c977441f@enterprisedb.com>
 <CAA4eK1Kp8TZ7PqzJBYMhfbQ+62wpYRh_E6eLSPAn=xsCEvOCOg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <acf0ba6e-1add-75da-a989-8e8fd82253e0@enterprisedb.com>
Date: Tue, 20 Jul 2021 12:13:13 +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: 
 <CAA4eK1Kp8TZ7PqzJBYMhfbQ+62wpYRh_E6eLSPAn=xsCEvOCOg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/acf0ba6e-1add-75da-a989-8e8fd82253e0%40enterprisedb.com>
Precedence: bulk

On 7/20/21 11:42 AM, Amit Kapila wrote:
> On Tue, Jul 20, 2021 at 2:39 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 7/20/21 7:23 AM, Amit Kapila wrote:
>>> On Mon, Jul 19, 2021 at 7:02 PM Tomas Vondra
>>> <tomas.vondra@enterprisedb.com> wrote:
>>
>>>> So maybe the best thing is to stick to the simple approach already used
>>>> e.g. by pglogical, which simply user the new row when available (insert,
>>>> update) and old one for deletes.
>>>>
>>>> I think that behaves more or less sensibly and it's easy to explain.
>>>>
>>>
>>> Okay, if nothing better comes up, then we can fall back to this option.
>>>
>>>> All the other things (e.g. turning UPDATE to INSERT, advanced conflict
>>>> resolution etc.) will require a lot of other stuff,
>>>>
>>>
>>> I have not evaluated this yet but I think spending some time thinking
>>> about turning Update to Insert/Delete (yesterday's suggestion by
>>> Alvaro) might be worth especially as that seems to be followed by some
>>> other replication solution as well.
>>>
>>
>> I think that requires quite a bit of infrastructure, and I'd bet we'll
>> need to handle other types of conflicts too.
>>
> 
> Hmm, I don't see why we need any additional infrastructure here if we
> do this at the publisher. I think this could be done without many
> changes to the patch as explained in one of my previous emails [1].
> 

Oh, I see. I've been thinking about doing the "usual" conflict
resolution on the subscriber side. I'm not sure about doing this on the
publisher ...

>> I don't have a clear
>> opinion if that's required to get this patch working - I'd try getting
>> the simplest implementation with reasonable behavior, with those more
>> advanced things as future enhancements.
>>
>>>> and I see them as
>>>> improvements of this simple approach.
>>>>
>>>>>>> Maybe a second option is to have replication change any UPDATE into
>>>>>>> either an INSERT or a DELETE, if the old or the new row do not pass the
>>>>>>> filter, respectively.  That way, the databases would remain consistent.
>>>>>
>>>>> Yeah, I think this is the best way to keep the data consistent.
>>>>>
>>>>
>>>> It'd also require REPLICA IDENTITY FULL, which seems like it'd add a
>>>> rather significant overhead.
>>>>
>>>
>>> Why? I think it would just need similar restrictions as we are
>>> planning for Delete operation such that filter columns must be either
>>> present in primary or replica identity columns.
>>>
>>
>> How else would you turn UPDATE to INSERT? For UPDATE we only send the
>> identity columns and modified columns, and the decision happens on the
>> subscriber.
>>
> 
> Hmm, we log the entire new tuple and replica identity columns for the
> old tuple in WAL for Update. And, we are going to use a new tuple for
> Insert, so we have everything we need.
> 

Do we log the TOAST-ed values that were not updated?


regards

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