PostgreSQL 15 preview - Allow specifying row filters for logical replication of tables.

原创 digoal 2022-01-20

264

作者

digoal

日期

2022-02-22

+CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);  
+CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert');  
+RESET client_min_messages;  
+\dRp+ testpub5  
+                                    Publication testpub5  
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root   
+--------------------------+------------+---------+---------+---------+-----------+----------  
+ regress_publication_user | f          | t       | f       | f       | f         | f  
+Tables:  
+    "public.testpub_rf_tbl1"  
+    "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))  
+  
+ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000);  
+\dRp+ testpub5  
+                                    Publication testpub5  
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root   
+--------------------------+------------+---------+---------+---------+-----------+----------  
+ regress_publication_user | f          | t       | f       | f       | f         | f  
+Tables:  
+    "public.testpub_rf_tbl1"  
+    "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))  
+    "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))  
+  
+ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2;  
+\dRp+ testpub5  
+                                    Publication testpub5  
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root   
+--------------------------+------------+---------+---------+---------+-----------+----------  
+ regress_publication_user | f          | t       | f       | f       | f         | f  
+Tables:  
+    "public.testpub_rf_tbl1"  
+    "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))  
+  
+-- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression)  
+ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500);  
+\dRp+ testpub5  
+                                    Publication testpub5  
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root   
+--------------------------+------------+---------+---------+---------+-----------+----------  
+ regress_publication_user | f          | t       | f       | f       | f         | f  
+Tables:  
+    "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500))

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=52e4f0cd472d39d07732b99559989ea3b615be78

Allow specifying row filters for logical replication of tables.  
master github/master  
author  Amit Kapila <akapila@postgresql.org>      
Tue, 22 Feb 2022 02:24:12 +0000 (07:54 +0530)  
committer   Amit Kapila <akapila@postgresql.org>      
Tue, 22 Feb 2022 02:41:50 +0000 (08:11 +0530)  
commit  52e4f0cd472d39d07732b99559989ea3b615be78  
tree    e40cc7b7690f82c7cfb945fd55afdf55e9bc944f    tree  
parent  ebf6c5249b7db525e59563fb149642665c88f747    commit | diff  
Allow specifying row filters for logical replication of tables.  
This feature adds row filtering for publication tables. When a publication  
is defined or modified, an optional WHERE clause can be specified. Rows  
that don't satisfy this WHERE clause will be filtered out. This allows a  
set of tables to be partially replicated. The row filter is per table. A  
new row filter can be added simply by specifying a WHERE clause after the  
table name. The WHERE clause must be enclosed by parentheses.  
The row filter WHERE clause for a table added to a publication that  
publishes UPDATE and/or DELETE operations must contain only columns that  
are covered by REPLICA IDENTITY. The row filter WHERE clause for a table  
added to a publication that publishes INSERT can use any column. If the  
row filter evaluates to NULL, it is regarded as "false". The WHERE clause  
only allows simple expressions that don't have user-defined functions,  
user-defined operators, user-defined types, user-defined collations,  
non-immutable built-in functions, or references to system columns. These  
restrictions could be addressed in the future.  
If you choose to do the initial table synchronization, only data that  
satisfies the row filters is copied to the subscriber. If the subscription  
has several publications in which a table has been published with  
different WHERE clauses, rows that satisfy ANY of the expressions will be  
copied. If a subscriber is a pre-15 version, the initial table  
synchronization won't use row filters even if they are defined in the  
publisher.  
The row filters are applied before publishing the changes. If the  
subscription has several publications in which the same table has been  
published with different filters (for the same publish operation), those  
expressions get OR'ed together so that rows satisfying any of the  
expressions will be replicated.  
This means all the other filters become redundant if (a) one of the  
publications have no filter at all, (b) one of the publications was  
created using FOR ALL TABLES, (c) one of the publications was created  
using FOR ALL TABLES IN SCHEMA and the table belongs to that same schema.  
If your publication contains a partitioned table, the publication  
parameter publish_via_partition_root determines if it uses the partition's  
row filter (if the parameter is false, the default) or the root  
partitioned table's row filter.  
Psql commands \dRp+ and \d <table-name> will display any row filters.  
Author: Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian  
Reviewed-by: Greg Nancarrow, Haiying Tang, Amit Kapila, Tomas Vondra, Dilip Kumar, Vignesh C, Alvaro Herrera, Andres Freund, Wei Wang  
Discussion: https://www.postgresql.org/message-id/flat/CAHE3wggb715X%2BmK_DitLXF25B%3DjE6xyNCH4YOwM860JR7HarGQ%40mail.gmail.com