Subject: Re: row filtering for logical replication
To: Ajin Cherian <itsajin@gmail.com>, Euler Taveira <euler@eulerto.com>
Cc: Peter Smith <smithpb2250@gmail.com>, Amit Kapila
 <amit.kapila16@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: 
 <CA+HiwqH+9NK5m-=BOYpzjH5UEpeWjT_3HEm4XBYNrda88naGMQ@mail.gmail.com>
 <CAA4eK1+qd1OATDSZCrjebZx+3qm1=RmF-fDU_1YsT__XYnxvVA@mail.gmail.com>
 <CAHut+Pvn+S8upyrkBi3m=rc4b5Ttw3xGsg09rNec=qJK0pD5mQ@mail.gmail.com>
 <CAA4eK1J1FNhDhj85Am1X_5VTxKOYo_nSBMuuMteB3n5YVPMdpw@mail.gmail.com>
 <CAHut+PtbKUmWNjTEDMVhP9abjgph6JB7vUFYF7ioa_4uRPZb=A@mail.gmail.com>
 <CAA4eK1LPhubcm4o1Xg0RwZdatqwfz3KKYcdun8QvRyqFthyJ1g@mail.gmail.com>
 <CAHut+PtFxoP477E8odkxkpyDoH_tiNBSiJH-j88NR728nxPErQ@mail.gmail.com>
 <CAA4eK1+tio46goUKBUfAKFsFVxtgk8nOty=TxKoKH-gdLzHD2g@mail.gmail.com>
 <CAHut+PtdRU74kUZd2RugdCqB0Ya1L+BbZk45czM5ufQs5E8jmw@mail.gmail.com>
 <CAHut+PsgRHymwLhJ9t3By6+KNaVDzfjf6Y4Aq=JRD-y8t1mEFg@mail.gmail.com>
 <0c2464d4-65f4-4d91-aeb2-c5584c1350f5@www.fastmail.com>
 <CAFPTHDb7bpkuc4SxaL9B5vEvF2aEi0EOERdrG+xgVeAyMJsF=Q@mail.gmail.com>
 <CAFPTHDYZk4P1kD=y6qMBnzh66+CgnfV3VTK7yS-Tk1eGrFOVZg@mail.gmail.com>
 <574b4e78-2f35-acf3-4bdc-4b872582e739@enterprisedb.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <2e1f3505-6533-f642-37bc-e75c15cfcde4@enterprisedb.com>
Date: Mon, 27 Sep 2021 15:34:08 +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: <574b4e78-2f35-acf3-4bdc-4b872582e739@enterprisedb.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2e1f3505-6533-f642-37bc-e75c15cfcde4%40enterprisedb.com>
Precedence: bulk

Hi,

I see no one responded to this important part of my review so far:

On 9/23/21 2:33 PM, Tomas Vondra wrote:
> 3) create_subscription.sgml
> 
>      <literal>WHERE</literal> clauses, rows must satisfy all expressions
>      to be copied. If the subscriber is a
> 
> I'm rather skeptical about the principle that all expressions have to 
> match - I'd have expected exactly the opposite behavior, actually.
> 
> I see a subscription as "a union of all publications". Imagine for 
> example you have a data set for all customers, and you create a 
> publication for different parts of the world, like
> 
>    CREATE PUBLICATION customers_france
>       FOR TABLE customers WHERE (country = 'France');
> 
>    CREATE PUBLICATION customers_germany
>       FOR TABLE customers WHERE (country = 'Germany');
> 
>    CREATE PUBLICATION customers_usa
>       FOR TABLE customers WHERE (country = 'USA');
> 
> and now you want to subscribe to multiple publications, because you want 
> to replicate data for multiple countries (e.g. you want EU countries). 
> But if you do
> 
>    CREATE SUBSCRIPTION customers_eu
>           PUBLICATION customers_france, customers_germany;
> 
> then you won't get anything, because each customer belongs to just a 
> single country. Yes, I could create multiple individual subscriptions, 
> one for each country, but that's inefficient and may have a different 
> set of issues (e.g. keeping them in sync when a customer moves between 
> countries).
> 
> I might have missed something, but I haven't found any explanation why 
> the requirement to satisfy all expressions is the right choice.
> 
> IMHO this should be 'satisfies at least one expression' i.e. we should 
> connect the expressions by OR, not AND.

Am I the only one finding the current behavior strange? What's the 
reasoning supporting the current approach?


regards

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