Message-ID: <47dd2cb9-4e96-169f-15ac-f9407fb54d43@enterprisedb.com>
Date: Mon, 2 May 2022 22:34:48 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.8.1
Subject: Re: bogus: logical replication rows/cols combinations
Content-Language: en-US
To: Tomas Vondra <tomas.vondra@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>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <338e719c-4bc8-f40a-f701-e29543a264e4@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/47dd2cb9-4e96-169f-15ac-f9407fb54d43%40enterprisedb.com>
Precedence: bulk

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.

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.

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.