Message-ID: <f9d138c4-ff80-42ca-3e27-f2ceb5424b50@enterprisedb.com>
Date: Sun, 1 May 2022 23:57:16 +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>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: 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>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CAA4eK1KrSsMPpoXY5-X1otTwhQzKq+ahgee4YakOK9xG-FYYwQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/f9d138c4-ff80-42ca-3e27-f2ceb5424b50%40enterprisedb.com>
Precedence: bulk

On 4/30/22 12:11, Amit Kapila wrote:
> On Sat, Apr 30, 2022 at 3:01 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>>
>> On 2022-Apr-30, Amit Kapila wrote:
>>
>>> On Sat, Apr 30, 2022 at 2:02 AM Tomas Vondra
>>> <tomas.vondra@enterprisedb.com> wrote:
>>
>>>> That seems to deal with a circular replication, i.e. two logical
>>>> replication links - a bit like a multi-master. Not sure how is that
>>>> related to the issue we're discussing here?
>>>
>>> It is not directly related to what we are discussing here but I was
>>> trying to emphasize the point that users need to define the logical
>>> replication via pub/sub sanely otherwise they might see some weird
>>> behaviors like that.
>>
>> I agree with that.
>>
>> My proposal is that if users want to define multiple publications, and
>> their definitions conflict in a way that would behave ridiculously (==
>> bound to cause data inconsistencies eventually), an error should be
>> thrown.  Maybe we will not be able to catch all bogus cases, but we can
>> be prepared for the most obvious ones, and patch later when we find
>> others.
>>
> 
> I agree with throwing errors for obvious/known bogus cases but do we
> want to throw errors or restrict the combining of column lists when
> row filters are present in all cases? See some examples [1 ] where it
> may be valid to combine them.
> 

I think there are three challenges:

(a) Deciding what's an obvious bug or an unsupported case (e.g. because
it's not clear what's the correct behavior / way to merge column lists).

(b) When / where to detect the issue.

(c) Making sure this does not break/prevent existing use cases.


As I said before [1], I think the issue stems from essentially allowing
DML to have different row filters / column lists. So we could forbid
publications to specify WITH (publish=...) and one of the two features,
or make sure subscription does not combine multiple such publications.

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.

Furthermore, what if the publications change after the subscriptions get
created? Will we be able to detect the error etc.?

So I'd prefer the first option, but maybe that prevents some useful use
cases too ...


regards


[1]
https://www.postgresql.org/message-id/45d27a8a-7c7a-88e8-a3db-c7c1d144df5e%40enterprisedb.com

[2]
https://www.postgresql.org/message-id/338e719c-4bc8-f40a-f701-e29543a264e4%40enterprisedb.com

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