Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@gmail.com>, Euler Taveira <euler@eulerto.com>
Cc: 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>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <d3592bf3-fb31-fd0e-592c-abfd3ae9d259@enterprisedb.com>
Date: Wed, 14 Jul 2021 12:28:16 +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: 
 <CAA4eK1+0-R8Q8gVJGho3E6Lyor=1LqRtUmY7bCY668FFyKvcTg@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/d3592bf3-fb31-fd0e-592c-abfd3ae9d259%40enterprisedb.com>
Precedence: bulk


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?

regards

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