Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@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>
 <CAA4eK1LnHscyn6dfAUJ4o3Bnw12sAgEmxMUZOzc3CjRM9BOCkA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <db552f21-b9b7-4631-1ea9-03a91379cc87@enterprisedb.com>
Date: Wed, 14 Jul 2021 16:33:49 +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: 
 <CAA4eK1LnHscyn6dfAUJ4o3Bnw12sAgEmxMUZOzc3CjRM9BOCkA@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/db552f21-b9b7-4631-1ea9-03a91379cc87%40enterprisedb.com>
Precedence: bulk

On 7/14/21 2:50 PM, Amit Kapila wrote:
> On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 7/14/21 7:39 AM, Amit Kapila wrote:
>>> On Wed, Jul 14, 2021 at 6:28 AM Euler Taveira <euler@eulerto.com> wrote:
>>>>
>>>> On Tue, Jul 13, 2021, at 6:06 PM, Alvaro Herrera wrote:
>>>>
>>>> 1. if you use REPLICA IDENTITY FULL, then the expressions would work
>>>> even if they use any other column with DELETE.  Maybe it would be
>>>> reasonable to test for this in the code and raise an error if the
>>>> expression requires a column that's not part of the replica identity.
>>>> (But that could be relaxed if the publication does not publish
>>>> updates/deletes.)
>>>>
>>>
>>> +1.
>>>
>>>> I thought about it but came to the conclusion that it doesn't worth it.  Even
>>>> with REPLICA IDENTITY FULL expression evaluates to false if the column allows
>>>> NULL values. Besides that REPLICA IDENTITY is changed via another DDL (ALTER
>>>> TABLE) and you have to make sure you don't allow changing REPLICA IDENTITY
>>>> because some row filter uses the column you want to remove from it.
>>>>
>>>
>>> Yeah, that is required but is it not feasible to do so?
>>>
>>>> 2. For UPDATE, does the expression apply to the old tuple or to the new
>>>> tuple?  You say it's the new tuple, but from the user point of view I
>>>> think it would make more sense that it would apply to the old tuple.
>>>> (Of course, if you're thinking that the R.I. is the PK and the PK is
>>>> never changed, then you don't really care which one it is, but I bet
>>>> that some people would not like that assumption.)
>>>>
>>>> New tuple. The main reason is that new tuple is always there for UPDATEs.
>>>>
>>>
>>> I am not sure if that is a very good reason to use a new tuple.
>>>
>>
>> True. Perhaps we should look at other places with similar concept of
>> WHERE conditions and old/new rows, and try to be consistent with those?
>>
>> I can think of:
>>
>> 1) updatable views with CHECK option
>>
>> 2) row-level security
>>
>> 3) triggers
>>
>> 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 apart from the above, it might be good if we can find what
> some other databases does in this regard?
> 

Yeah, that might tell us what the users would like to do with it. I did 
some quick search, but haven't found much :-( The one thing I found is 
that Debezium [1] allows accessing both the "old" and "new" rows through 
value.before and value.after, and use both for filtering.

I haven't found much about how this works in other databases, sadly.

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.


[1] 
https://wanna-joke.com/wp-content/uploads/2015/01/german-translation-comics-science.jpg

regards

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