Horton
Menu

Re: Column Filtering in Logical Replication

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Rahila Syed <rahilasyed90@gmail.com>, Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-07-12T12:42:55Z
Lists: pgsql-hackers 

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching