Re: row filtering for logical replication

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?

regards

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