Re: row filtering for logical replication

On 7/14/21 2:50 PM, Amit Kapila wrote:
> On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>>
>> On 7/14/21 7:39 AM, Amit Kapila wrote:
>>> On Wed, Jul 14, 2021 at 6:28 AM Euler Taveira <euler(at)eulerto(dot)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