Subject: Re: Column Filtering in Logical Replication
To: Rahila Syed <rahilasyed90@gmail.com>, Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CAH2L28vddB_NFdRVpuyRBJEBWjz4BSyTB=_ektNRH8NJ1jf95g@mail.gmail.com>
 <CAHut+PuP_hXzJQtLOvyaCxEPySPqhFheO2gZA3RcugMMfz3RSQ@mail.gmail.com>
 <CAH2L28vZzcKQPV18EuwZG0wUr=E5g+E9WEbejtX-hEpjvJLLvg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <c9b78de0-383b-a61e-2f38-52398e7636cf@enterprisedb.com>
Date: Mon, 12 Jul 2021 14:42:55 +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: 
 <CAH2L28vZzcKQPV18EuwZG0wUr=E5g+E9WEbejtX-hEpjvJLLvg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/c9b78de0-383b-a61e-2f38-52398e7636cf%40enterprisedb.com>
Precedence: bulk


On 7/12/21 10:32 AM, Rahila Syed wrote:
> Hi Peter,
> 
>     Hi, I was wondering if/when a subset of cols is specified then does
>     that mean it will be possible for the table to be replicated to a
>     *smaller* table at the subscriber side? 
> 
>     e.g Can a table with 7 cols replicated to a table with 2 cols?
> 
>     table tab1(a,b,c,d,e,f,g) --> CREATE PUBLICATION pub1 FOR TABLE
>     tab1(a,b)  --> table tab1(a,b)
> 
>     ~~
> 
> 
>     I thought maybe that should be possible, but the expected behaviour
>     for that scenario was not very clear to me from the thread/patch
>     comments. And the new TAP test uses the tab1 table created exactly the
>     same for pub/sub, so I couldn't tell from the test code either.
> 
>  
> Currently, this capability is not included in the patch. If the table on
> the subscriber
> server has lesser attributes than that on the publisher server, it
> throws an error at the 
> time of CREATE SUBSCRIPTION.
> 

That's a bit surprising, to be honest. I do understand the patch simply
treats the filtered columns as "unchanged" because that's the simplest
way to filter the *data* of the columns. But if someone told me we can
"filter columns" I'd expect this to work without the columns on the
subscriber.

> About having such a functionality, I don't immediately see any issue
> with it as long
> as we make sure replica identity columns are always present on both
> instances.

Yeah, that seems like an inherent requirement.

> However, need to carefully consider situations in which a server
> subscribes to multiple 
> publications,  each publishing a different subset of columns of a table.  
>  

Isn't that pretty much the same situation as for multiple subscriptions
each with a different set of I/U/D operations? IIRC we simply merge
those, so why not to do the same thing here and merge the attributes?


regards

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