Message-ID: <a7a5e79f-053a-cff8-6b2c-f7ac4bab9920@enterprisedb.com>
Date: Tue, 3 May 2022 21:40:04 +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: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Amit Kapila <amit.kapila16@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Pg Hackers <pgsql-hackers@lists.postgresql.org>
References: <006d5ffc-1e7a-5cf2-c507-88eee6e0c14d@enterprisedb.com>
 <202204270943.4ol7indtwv3i@alvherre.pgsql>
 <CAA4eK1+9LCX3qKyo9S4uaaciM0WqZrP6m=kMzufgpuv=a=PgXQ@mail.gmail.com>
 <39077967-c327-eddb-ac4b-7d64117944fb@enterprisedb.com>
 <CAA4eK1K+PkkC6_FDemGMC_i+Aakx+3=QG-g4We3BdCK7dK_bgA@mail.gmail.com>
 <338e719c-4bc8-f40a-f701-e29543a264e4@enterprisedb.com>
 <47dd2cb9-4e96-169f-15ac-f9407fb54d43@enterprisedb.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <47dd2cb9-4e96-169f-15ac-f9407fb54d43@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a7a5e79f-053a-cff8-6b2c-f7ac4bab9920%40enterprisedb.com>
Precedence: bulk


On 5/2/22 22:34, Peter Eisentraut wrote:
> On 01.05.22 23:42, Tomas Vondra wrote:
>> Imagine have a table with customers from different regions, and you want
>> to replicate the data somewhere else, but for some reason you can only
>> replicate details for one particular region, and subset of columns for
>> everyone else. So you'd do something like this:
>>
>> CREATE PUBLICATION p1 FOR TABLE customers (... all columns ...)
>>   WHERE region = 'USA';
>>
>> CREATE PUBLICATION p1 FOR TABLE customers (... subset of columns ...)
>>   WHERE region != 'USA';
>>
>> I think ignoring the row filters and just merging the column lists makes
>> no sense for this use case.
> 
> I'm thinking now the underlying problem is that we shouldn't combine
> column lists at all.  Examples like the above where you want to redact
> values somehow are better addressed with something like triggers or an
> actual "column filter" that works dynamically or some other mechanism.
> 

So what's wrong with merging the column lists as implemented in the v2
patch, posted a couple days ago?

I don't think triggers are a suitable alternative, as it executes on the
subscriber node. So you have to first copy the data to the remote node,
where it gets filtered. With column filters the data gets redacted on
the publisher.


> The main purpose, in my mind, of column lists is if the tables
> statically have different shapes on publisher and subscriber.  Perhaps
> for space reasons or regulatory reasons you don't want to replicate
> everything.  But then it doesn't make sense to combine column lists.  If
> you decide over here that the subscriber table has this shape and over
> there that the subscriber table has that other shape, then the
> combination of the two will be a table that has neither shape and so
> will not work for anything.
> 

Yeah. If we intend to use column lists only to adapt to a different
schema on the subscriber node, then maybe it'd be fine to not merge
column lists. It'd probably be reasonable to allow at least cases with
multiple publications using the same column list, though. In that case
there's no ambiguity.

> I think in general we should be much more restrictive in how we combine
> publications.  Unless we are really sure it makes sense, we should
> disallow it.  Users can always make a new publication with different
> settings and subscribe to that directly.

I agree with that in principle - correct first, flexibility second. If
the behavior is not correct, it doesn't matter how flexible it is.

I still think the data redaction use case is valid/interesting, but if
we want to impose some restrictions I'm OK with that, as long as it's
done in a way that we can relax in the future to allow that use case
(that is, without introducing any incompatibilities).

However, what's the definition of "correctness" in this context? Without
that it's hard to say if the restrictions make the behavior any more
correct. It'd be unfortunate to impose restritions, which will prevent
some use cases, only to discover we haven't actually made it correct.

For example, is it enough to restrict column lists, or does it need to
restrict e.g. row filters too? And does it need to consider other stuff,
like publications replicating different actions?

For example, if we allow different column lists (or row filters) for
different actions (one publication for insert, another one for update),
we still have the strange behavior described before.

And if we force users to use separate subscriptions, I'm not sure that
really improves the situation for users who actually need that. They'll
do that, and aside from all the problems they'll also face issues with
timing between the two concurrent subscriptions, having to decode stuff
multiple times, etc.


regards

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