Message-ID: <7fbca40e-b6d0-cd03-e2fc-1338994a8360@enterprisedb.com>
Date: Mon, 2 May 2022 20:37:59 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.8.0
Subject: Re: bogus: logical replication rows/cols combinations
Content-Language: en-US
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Pg Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CAA4eK1JXDzOu5jTCYM-TcqE2i3iiFJQoWW_qoVGgZ=vMgEHi4w@mail.gmail.com>
 <202204300931.um766ighsghq@alvherre.pgsql>
 <CAA4eK1KrSsMPpoXY5-X1otTwhQzKq+ahgee4YakOK9xG-FYYwQ@mail.gmail.com>
 <f9d138c4-ff80-42ca-3e27-f2ceb5424b50@enterprisedb.com>
 <CAA4eK1LGX-ig==QyL+==nKvcAS3qFU7=NiKL77ukUT-Q_4XncQ@mail.gmail.com>
 <7fdf7ac3-912b-f450-f9c1-aa239df4ac1a@enterprisedb.com>
 <CAA4eK1K33qZKQaYsTJHp3bQS4z8-2jsOaRmkjHXSH1SRA7C7TQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAA4eK1K33qZKQaYsTJHp3bQS4z8-2jsOaRmkjHXSH1SRA7C7TQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7fbca40e-b6d0-cd03-e2fc-1338994a8360%40enterprisedb.com>
Precedence: bulk


On 5/2/22 13:23, Amit Kapila wrote:
> On Mon, May 2, 2022 at 3:05 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 5/2/22 07:31, Amit Kapila wrote:
>>> On Mon, May 2, 2022 at 3:27 AM Tomas Vondra
>>> <tomas.vondra@enterprisedb.com> wrote:
>>>>
>>
>>>> The second option has the annoying consequence that it makes this
>>>> useless for the "data redaction" use case I described in [2], because
>>>> that relies on combining multiple publications.
>>>>
>>>
>>> True, but as a workaround users can create different subscriptions for
>>> different publications.
>>>
>>
>> Won't that replicate duplicate data, when the row filters re not
>> mutually exclusive?
>>
> 
> True, but this is a recommendation for mutually exclusive data, and as
> far as I can understand the example given by you [1] and Alvaro has
> mutually exclusive conditions. In your example, one of the
> publications has a condition (region = 'USA') and the other
> publication has a condition (region != 'USA'), so will there be a
> problem in using different subscriptions for such cases?
> 

I kept that example intentionally simple, but I'm sure we could come up
with more complex use cases. Following the "data redaction" idea, we
could also apply the "deny all" approach, and do something like this:

-- replicate the minimal column list by default (replica identity)
CREATE PUBLICATION p1 FOR TABLE t (id, region);

-- replicate more columns for the selected region
CREATE PUBLICATION p2 FOR TABLE t (...) WHERE (region = 'USA')

Now, I admit this is something I just made up, but I think it seems like
a pretty common approach.


regards

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