Re: Column Filtering in Logical Replication

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