From 47eab24dc65167ff4cd8c35fa86f596139f0afa3 Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Fri, 8 Apr 2022 15:45:27 +1000 Subject: [PATCH v6] PG DOCS page for row filters. This patch introduces a new documentation page for describing the "Row Filters" feature. Author: Peter Smith, Euler Taveira Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com --- doc/src/sgml/logical-replication.sgml | 551 +++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_publication.sgml | 2 + 2 files changed, 553 insertions(+) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 555fbd7..6e23be9 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -118,6 +118,8 @@ any combination of INSERT, UPDATE, DELETE, and TRUNCATE, similar to how triggers are fired by particular event types. By default, all operation types are replicated. + (Row filters have no effect for TRUNCATE. See + ). @@ -317,6 +319,555 @@ + + Row Filters + + + By default, all data from all published tables will be replicated to the + appropriate subscribers. + + + + The replicated data can be reduced by using a row filter. + A user might choose to use row filters for behavioral, security or performance + reasons. + + + + If a published table sets a row filter, a row is replicated only if its data + satisfies the row filter expression. This allows a set of tables to be + partially replicated. + + + + The row filter is defined per table. Use a WHERE clause + after the table name for each published table that requires data to be + filtered out. The WHERE clause must be enclosed by + parentheses. See for details. + + + + Row Filter Rules + + + Row filters are applied before publishing the changes. + + + + If the row filter evaluates to false or + NULL then the row is not replicated. + + + + The WHERE clause expression is evaluated with the same + role used for the replication connection (i.e. the role specified in the + CONNECTION clause of the ). + + + + Row filters have no effect for TRUNCATE command. + + + + + + Expression Restrictions + + + The WHERE clause allows only simple expressions. It + cannot contain user-defined functions, operators, types, and collations, + system column references or non-immutable built-in functions. + + + + If a publication publishes UPDATE and/or + DELETE operations, the row filter WHERE clause + must contain only columns that are covered by the replica identity (see + ). If a publication publishes only + INSERT, the row filter WHERE clause + can use any column. + + + + + + UPDATE Transformations + + + Whenever an UPDATE is processed, the row filter + expression is evaluated for both the old and new row (i.e. using the data + before and after the update). + + + + If both evaluations are true, it replicates the + UPDATE change. + + + + If both evaluations are false, it doesn't replicate + the change. + + + + If only one of the old/new rows matches the row filter expression, the + UPDATE is transformed to INSERT or + DELETE, to avoid any data inconsistency. The row on the + subscriber should reflect what is defined by the row filter expression on + the publisher. + + + + + If the old row satisfies the row filter expression (it was sent to the + subscriber) but the new row doesn't, then from a data consistency + perspective the old row should be removed from the subscriber. + So the UPDATE is transformed into a DELETE. + + + + + If the old row doesn't satisfy the row filter expression (it wasn't sent + to the subscriber) but the new row does, then from a data consistency + perspective the new row should be added to the subscriber. + So the UPDATE is transformed into an INSERT. + + + + +Summary: + +Case 1: old-row (no match) new-row (no match) --> (drop change) +Case 2: old-row (no match) new row (match) --> INSERT +Case 3: old-row (match) new-row (no match) --> DELETE +Case 4: old-row (match) new row (match) --> UPDATE + + + + + + + Partitioned Tables + + + If the publication contains a partitioned table, the publication parameter + publish_via_partition_root determines which row filter + is used. + + + + + If publish_via_partition_root is false + (default), each partition's row filter is used. + + + + + + If publish_via_partition_root is true, + the root partitioned table's row filter is used. + + + + + + + + + + Initial Data Synchronization + + + If the subscription requires copying pre-existing table data + and a publication contains WHERE clauses, only data that + satisfies the row filter expressions 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. (see + for details). + + + + + Publication publish operations are ignored when copying pre-existing table data. + + + + + + If the subscriber is in a release prior to 15, copy pre-existing data + doesn't use row filters even if they are defined in the publication. + This is because old releases can only copy the entire table data. + + + + + + + Combining Multiple Row Filters + + + If the subscription has several publications in which the same table has + been published with different row 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 row filters for the same table become redundant if: + + + + + one of the publications has no row filter. + + + + + + one of the publications was created using FOR ALL TABLES. + This clause does not allow row filters. + + + + + + one of the publications was created using + FOR ALL TABLES IN SCHEMA and the table belongs to + the referred schema. This clause does not allow row filters. + + + + + + + + + + Examples + + + Create some tables to be used in the following examples. + +testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c)); +CREATE TABLE +testpub=# CREATE TABLE t2(d int, e int, f int, primary key(d)); +CREATE TABLE +testpub=# CREATE TABLE t3(g int, h int, i int, primary key(g)); +CREATE TABLE + + + + + Create some publications. + +testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW'); +CREATE PUBLICATION +testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99); +CREATE PUBLICATION +testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10); +CREATE PUBLICATION + + + + + Publication p1 has 1 table (t1) and + that table has a row filter. + + + + + Publication p2 has 2 tables. Table t1 + has no row filter, and table t2 has a row filter. + + + + + Publication p3 has 2 tables, and both of them have a + row filter. + + + + + + + The PSQL command \dRp+ shows the row filter expressions + (if defined) for each table of the publications. + +testpub=# \dRp+ + Publication p1 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +----------+------------+---------+---------+---------+-----------+---------- + postgres | f | t | t | t | t | f +Tables: + "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text)) + + Publication p2 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +----------+------------+---------+---------+---------+-----------+---------- + postgres | f | t | t | t | t | f +Tables: + "public.t1" + "public.t2" WHERE (e = 99) + + Publication p3 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +----------+------------+---------+---------+---------+-----------+---------- + postgres | f | t | t | t | t | f +Tables: + "public.t2" WHERE (d = 10) + "public.t3" WHERE (g = 10) + + + + + The PSQL command \d shows what publications the table is + a member of, as well as that table's row filter expression (if defined) in + those publications. + +testpub=# \d t1 + Table "public.t1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | | + c | text | | not null | +Indexes: + "t1_pkey" PRIMARY KEY, btree (a, c) +Publications: + "p1" WHERE ((a > 5) AND (c = 'NSW'::text)) + "p2" + +testpub=# \d t2 + Table "public.t2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + d | integer | | not null | + e | integer | | | + f | integer | | | +Indexes: + "t2_pkey" PRIMARY KEY, btree (d) +Publications: + "p2" WHERE (e = 99) + "p3" WHERE (d = 10) + +testpub=# \d t3 + Table "public.t3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + g | integer | | not null | + h | integer | | | + i | integer | | | +Indexes: + "t3_pkey" PRIMARY KEY, btree (g) +Publications: + "p3" WHERE (g = 10) + + + + + Table t1 is a member of 2 publications, but + has a row filter only in p1. + + + + + Table t2 is a member of 2 publications, and + has a different row filter in each of them. + + + + + + + On the subscriber node, create a table t1 with the same + definition as the one on the publisher, and also create the subscription + s1 that subscribes to the publication p1. + +testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c)); +CREATE TABLE +testsub=# CREATE SUBSCRIPTION s1 +testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s1' +testsub-# PUBLICATION p1; +CREATE SUBSCRIPTION + + + + + Insert some rows. + +testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (7, 107, 'NT'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD'); +INSERT 0 1 +testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW'); +INSERT 0 1 + +testpub=# SELECT * FROM t1; + a | b | c +---+-----+----- + 2 | 102 | NSW + 3 | 103 | QLD + 4 | 104 | VIC + 5 | 105 | ACT + 6 | 106 | NSW + 7 | 107 | NT + 8 | 108 | QLD + 9 | 109 | NSW +(8 rows) + + +testsub=# SELECT * FROM t1; + a | b | c +---+-----+----- + 6 | 106 | NSW + 9 | 109 | NSW +(2 rows) + + + + + Only the rows satisfying the t1 WHERE + clause of publication p1 are replicated. + + + + + + + Update some data, where the old and new row values both + satisfy the t1 WHERE clause of publication + p1. + +testpub=# UPDATE t1 SET b = 999 WHERE a = 6; +UPDATE 1 + +testpub=# SELECT * FROM t1; + a | b | c +---+-----+----- + 2 | 102 | NSW + 3 | 103 | QLD + 4 | 104 | VIC + 5 | 105 | ACT + 7 | 107 | NT + 8 | 108 | QLD + 9 | 109 | NSW + 6 | 999 | NSW +(8 rows) + + +testsub=# SELECT * FROM t1; + a | b | c +---+-----+----- + 9 | 109 | NSW + 6 | 999 | NSW +(2 rows) + + + + + The UPDATE replicates the change as normal. + + + + + + + Update some data, where the old row values did not satisfy + the t1 WHERE clause of publication p1, + but the new row values do satisfy it. + +testpub=# UPDATE t1 SET a = 555 WHERE a = 2; +UPDATE 1 + +testpub=# SELECT * FROM t1; + a | b | c +-----+-----+----- + 3 | 103 | QLD + 4 | 104 | VIC + 5 | 105 | ACT + 7 | 107 | NT + 8 | 108 | QLD + 9 | 109 | NSW + 6 | 999 | NSW + 555 | 102 | NSW +(8 rows) + + +testsub=# SELECT * FROM t1; + a | b | c +-----+-----+----- + 9 | 109 | NSW + 6 | 999 | NSW + 555 | 102 | NSW +(3 rows) + + + + + The UPDATE is transformed into an INSERT + and the change is replicated. + + + + + + + Update some data, where the old row values satisfied + the t1 WHERE clause of publication p1, + but the new row values do not satisfy it. + +testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9; +UPDATE 1 + +testpub=# SELECT * FROM t1; + a | b | c +-----+-----+----- + 3 | 103 | QLD + 4 | 104 | VIC + 5 | 105 | ACT + 7 | 107 | NT + 8 | 108 | QLD + 6 | 999 | NSW + 555 | 102 | NSW + 9 | 109 | VIC +(8 rows) + + +testsub=# SELECT * FROM t1; + a | b | c +-----+-----+----- + 6 | 999 | NSW + 555 | 102 | NSW +(2 rows) + + + + + The UPDATE is transformed into a DELETE + and the change is replicated. + + + + + + + + + Conflicts diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index fb2d013..23d883c 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -254,6 +254,8 @@ CREATE PUBLICATION name 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. + See for details about row + filters. -- 1.8.3.1