Subject: Re: row filtering for logical replication
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Amit Kapila
 <amit.kapila16@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: <202107141452.edncq4ot5zkg@alvherre.pgsql>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <4da2b027-4f80-f31f-1d06-6498e9760c2e@enterprisedb.com>
Date: Wed, 14 Jul 2021 17:08:34 +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: <202107141452.edncq4ot5zkg@alvherre.pgsql>
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/4da2b027-4f80-f31f-1d06-6498e9760c2e%40enterprisedb.com>
Precedence: bulk


On 7/14/21 4:52 PM, Alvaro Herrera wrote:
> On 2021-Jul-14, Tomas Vondra wrote:
> 
>> The way I'm thinking about this is that for INSERT and DELETE it's clear
>> which row version should be used (because there's just one). And for UPDATE
>> we could see that as DELETE + INSERT, and apply the same rule to each
>> action.
>>
>> On the other hand, I can imagine cases where it'd be useful to send the
>> UPDATE when the old row matches the condition and new row does not.
> 
> In any case, it seems to me that the condition expression should be
> scanned to see which columns are used in Vars (pull_varattnos?), and
> verify if those columns are in the REPLICA IDENTITY; and if they are
> not, raise an error.  Most of the time the REPLICA IDENTITY is going to
> be the primary key; but if the user wants to use other columns in the
> expression, we can HINT that they can set REPLICA IDENTITY FULL.
> 

Yeah, but AFAIK that's needed only when replicating DELETEs, so perhaps 
we could ignore this for subscriptions without DELETE.

The other question is when to check/enforce this. I guess we'll have to 
do that during decoding, not just when the publication is being created, 
because the user can do ALTER TABLE later.


regards

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