From aea0c822489fbdeba14141e215f76e3777521bb2 Mon Sep 17 00:00:00 2001 From: Euler Taveira Date: Thu, 10 Mar 2022 17:38:24 -0300 Subject: [PATCH v4] doc: new section for row filter. --- doc/src/sgml/logical-replication.sgml | 444 +++++++++++++++++++++++ doc/src/sgml/ref/create_publication.sgml | 2 + 2 files changed, 446 insertions(+) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 82326c3901..38ac77b6e1 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,448 @@ + + Row Filter + + + The published tables replicate all data to the subscribers. The replicated + data can be selected using a row filter. The 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 if its data + satisfies the row filter expression. It means if row filters are used in a + set of tables, data will 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 filter is 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. It means the role specified in the + CONNECTION clause of the . + + + + Row filter has 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 and non-immutable built-in functions. + + + + If a publication publishes update or + delete operations, the WHERE clause + must contain only columns that are part of the replica identity (see + ). If it publishes only + insert operation, the expression can use any column. + + + + + + UPDATE Transformations + + + Whenever an UPDATE is processed, the row filter + expression is evaluated for the old and new row. 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 rows matches the row filter expression, a + transformation is required. UPDATE should be transformed + into a DELETE or INSERT to avoid 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, after the UPDATE, + from a data consistency perspective, the old row should be removed from the + subscriber. Hence, UPDATE should be transformed into a + DELETE. + + + + On the other hand, if the old row doesn't satisfy the row filter expression + (it wasn't sent to the subscriber) but the new row does, after the + UPDATE, from a data consistency perspective, that new row + should be inserted into the subscriber. Hence, UPDATE + should be transformed into an INSERT. + + + + + + Partitioned Table + + + If the publication contains a partitioned table, the parameter + publish_via_partition_root determines which row filter + expression is used. If the parameter + publish_via_partition_root is true, the row filter + expression associated with the partitioned table is used. Otherwise, the row + filter expression associated with the individual partition is used. + + + + + + Initial Data Synchronization + + + If the subscription requires copying pre-existing data in the publications + 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). + + + + If the subscriber is in a release prior to 15, copy pre-existing data + doesn't use row filter expressions even if it is 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), rows that satisfy any of the row + filter expressions is replicated. It means all other row filters for the + same table become irrelevant if: + + + + + one of the publications has no row filter; + + + + + one of the publications was created using + FOR ALL TABLES clause. This clause does not allow row + filters; + + + + + one of the publications was created using + FOR ALL TABLES IN SCHEMA clause 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 + + + + + The publication p1 contains only one table + (t1) that has a row filter expression for it. + + + + The publication p2 contains 2 tables. The table + t1 has no row filter and the table t2 + has a row filter. + + + + The publication p3 contains 2 tables and each one has a + defined row filter. + + + + The psql shows the row filter expressions (if defined) + for each table. + + +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) + +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) + + + + + On the subscriber, create the 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=10.1.2.3 port=5432 user=repuser dbname=testpub' +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) + + + + + Notice that only the rows satisfying the t1 + WHERE clause of the publication + p1 are replicated. + +testsub=# SELECT * FROM t1; + a | b | c +---+-----+----- + 6 | 106 | NSW + 9 | 109 | NSW +(2 rows) + + + + + Update a row whose both (old and new) rows satisfy the t1 + WHERE clause of the publication p1. It + replicates the UPDATE as expected. + +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) + + + + + Update a row whose the old row doesn't satisfy the t1 + WHERE clause of the publication p1 + but the new row satisfies it. The UPDATE is transformed + into an INSERT and the change is replicated. See the new + row on the subscriber. + +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) + + + + + Update a row whose the old row satisfies the t1 + WHERE clause of the publication p1 + but the new row doesn't satisfy it. The UPDATE is + transformed into a DELETE and the change is replicated. + See that the row is removed from the subscriber. + +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 + 999 | 102 | NSW + 9 | 109 | VIC +(8 rows) + + +testsub=# SELECT * FROM t1; + a | b | c +-----+-----+----- + 6 | 999 | NSW + 999 | 102 | NSW +(2 rows) + + + + + + + Conflicts diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index 4979b9b646..3591d51af3 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -247,6 +247,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. -- 2.30.2