Postgresql 中文操作指南
31.3. Row Filters #
默认情况下，从所有已发布表的全部数据都将复制到相应的订阅者。可以通过使用 row filter 来减少复制的数据。用户可能会出于行为、安全或性能原因选择使用行过滤器。如果已发布表设置了行过滤器，只有当一行的数据满足行过滤器表达式时，该行才会被复制。这允许对一组表进行部分复制。行过滤器是按表定义的。对于需要将数据筛选出的每个已发布表，请在表名后使用 WHERE 子句。 WHERE 子句必须用括号括起来。有关详细信息，请参见 CREATE PUBLICATION 。
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 CREATE PUBLICATION for details.
31.3.1. Row Filter Rules #
在发布更改时应用行过滤器 before 。如果行过滤器求值为 false 或 NULL ，则不会复制该行。使用用于复制连接（即 CREATE SUBSCRIPTION 的 CONNECTION 子句中指定的角色）的相同角色评估 WHERE 子句表达式。行过滤器对 TRUNCATE 命令没有影响。
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 CREATE SUBSCRIPTION). Row filters have no effect for TRUNCATE command.
31.3.2. Expression Restrictions #
_WHERE_子句仅允许简单表达式。它不能包含用户定义的函数、运算符、类型和校对、系统列引用或不可变的内置函数。
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.
如果发布发布了 UPDATE 或 DELETE 操作，则行过滤器 WHERE 子句只能包含复制标识覆盖的列（参见 REPLICA IDENTITY ）。如果发布仅发布 INSERT 操作，则行过滤器 WHERE 子句可以使用任何列。
If a publication publishes UPDATE or DELETE operations, the row filter WHERE clause must contain only columns that are covered by the replica identity (see REPLICA IDENTITY). If a publication publishes only INSERT operations, the row filter WHERE clause can use any column.
31.3.3. UPDATE Transformations #
每当处理_UPDATE_时，都会针对新旧两行计算行过滤器表达式（即，使用更新之前和更新之后的数据）。如果两个计算结果都为_true_，则复制_UPDATE_更改。如果两个计算结果都为_false_，则不复制更改。如果只有新旧行之一与行过滤器表达式匹配，则_UPDATE_转换为_INSERT_或_DELETE_，以避免任何数据不一致。订阅者上的行应反映发布者上行过滤器表达式的定义。
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.
如果旧行满足行过滤器表达式（已发送给订阅者）但新行不满足，那么从数据一致性的角度来看，应从订阅者中删除旧行。所以_UPDATE_被转换为_DELETE_。
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.
如果旧行不满足行过滤器表达式（未发送给订阅者）但新行满足，那么从数据一致性的角度来看，应将新行添加到订阅者中。所以_UPDATE_被转换为_INSERT_。
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.
Table 31.1总结了应用的转换。
Table 31.1 summarizes the applied transformations.
Table 31.1. UPDATE Transformation Summary
Table 31.1. UPDATE Transformation Summary
Old row |
New row |
Transformation |
no match |
no match |
don’t replicate |
no match |
match |
INSERT |
match |
no match |
DELETE |
match |
match |
UPDATE |
31.3.4. Partitioned Tables #
如果发布包含分区表，则发布参数 publish_via_partition_root 确定使用哪个行过滤器。如果 publish_via_partition_root 为 true ，则使用 root partitioned table’s 行过滤器。否则，如果 publish_via_partition_root 为 false （默认值），则使用每个 partition’s 行过滤器。
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 true, the root partitioned table’s row filter is used. Otherwise, if publish_via_partition_root is false (default), each partition’s row filter is used.
31.3.5. Initial Data Synchronization #
如果订阅需要进行现有表数据的复制，并且发布中包含_WHERE_子句，则只有满足行过滤器表达式的才能复制到订阅者中。
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.
如果订阅中有多个发布，其中表已使用不同的_WHERE_从句发布，则将复制满足表达式_any_的行。有关详情，请参阅 Section 31.3.6。
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 Section 31.3.6 for details.
Warning
由于初始数据同步在复制现有表数据时不考虑 publish 参数，因此可能会复制一些使用 DML 不会复制的行。请参阅 Section 31.7.1 ，并参见 Section 31.2.2 以获取示例。
Because initial data synchronization does not take into account the publish parameter when copying existing table data, some rows may be copied that would not be replicated using DML. Refer to Section 31.7.1, and see Section 31.2.2 for examples.
Note
如果订阅者处于 15 之前的版本中，则复制已存在的数据不会使用行过滤器，即使它们在发布物中已定义。这是因为旧版本只能复制整个表数据。
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.
31.3.6. Combining Multiple Row Filters #
如果某个订阅中的多个出版物中发布了具有不同行筛选器的相同表（对于相同的 publish 操作），这些表达式将按 OR 组合，以便满足这些表达式中的 any 的行得到复制。这意味着如果出现以下情况，同一表的所有其他行筛选器将变为冗余：
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 ORed 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:
31.3.7. Examples #
创建一些表用于以下示例中。
Create some tables to be used in the following examples.
test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
CREATE TABLE
test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
CREATE TABLE创建一些发布。发布_p1_有一个表（t1），该表有一个行过滤器。发布_p2_有两个表。表_t1_没有行过滤器，而表_t2_有一个行过滤器。发布_p3_有两个表，两个表均有一个行过滤器。
Create some publications. Publication p1 has one table (t1) and that table has a row filter. Publication p2 has two tables. Table t1 has no row filter, and table t2 has a row filter. Publication p3 has two tables, and both of them have a row filter.
test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
CREATE PUBLICATION可以利用_psql_来展示针对每个发布的行过滤器表达式（如果已定义）。
psql can be used to show the row filter expressions (if defined) for each publication.
test_pub=# \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)可以利用_psql_来展示针对每个表的行过滤器表达式（如果已定义）。请注意表_t1_是两个发布的成员，但仅在_p1_中有一个行过滤器。请注意表_t2_是两个发布的成员，而且在每个发布中都有一个不同的行过滤器。
psql can be used to show the row filter expressions (if defined) for each table. See that table t1 is a member of two publications, but has a row filter only in p1. See that table t2 is a member of two publications, and has a different row filter in each of them.
test_pub=# \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"
test_pub=# \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)
test_pub=# \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)在订阅者节点中，创建一个表_t1_，其定义与发布者上的一样，并创建一个订阅_s1_，该订阅订阅发布_p1_。
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.
test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION插入一些行。仅复制满足发布_p1_的_t1 WHERE_子句的行。
Insert some rows. Only the rows satisfying the t1 WHERE clause of publication p1 are replicated.
test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
INSERT 0 1
test_pub=# 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)test_sub=# SELECT * FROM t1;
a | b | c
---+-----+-----
6 | 106 | NSW
9 | 109 | NSW
(2 rows)更新一些数据，原有行和新行值均满足发布 t1 WHERE 的 p1 子句。UPDATE 照常复制变更。
Update some data, where the old and new row values both satisfy the t1 WHERE clause of publication p1. The UPDATE replicates the change as normal.
test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
UPDATE 1
test_pub=# 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)test_sub=# SELECT * FROM t1;
a | b | c
---+-----+-----
9 | 109 | NSW
6 | 999 | NSW
(2 rows)更新一些数据，原行值未满足发布 t1 WHERE 的 p1 子句，但新行值满足。UPDATE 转换为 INSERT，并复制变更。在订阅者中查看新行。
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. The UPDATE is transformed into an INSERT and the change is replicated. See the new row on the subscriber.
test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
UPDATE 1
test_pub=# 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)test_sub=# SELECT * FROM t1;
a | b | c
-----+-----+-----
9 | 109 | NSW
6 | 999 | NSW
555 | 102 | NSW
(3 rows)更新一些数据，原行值满足发布 t1 WHERE 的 p1 子句，但新行值不满足。UPDATE 转换为 DELETE，并复制变更。查看该行在订阅者中被删除。
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. The UPDATE is transformed into a DELETE and the change is replicated. See that the row is removed from the subscriber.
test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
UPDATE 1
test_pub=# 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)test_sub=# SELECT * FROM t1;
a | b | c
-----+-----+-----
6 | 999 | NSW
555 | 102 | NSW
(2 rows)以下示例展示了在分区表的情况下，出版物参数 publish_via_partition_root 决定使用父表或子表的行筛选器的方式。
The following examples show how the publication parameter publish_via_partition_root determines whether the row filter of the parent or child table will be used in the case of partitioned tables.
在发布者中创建分区表。
Create a partitioned table on the publisher.
test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE在订户上创建相同的表。
Create the same tables on the subscriber.
test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE创建发布 p4，然后订阅该发布。发布时间参数 publish_via_partition_root 设置为 true。在分区表和分区中定义了行筛选器（parent 和 child）。
Create a publication p4, and then subscribe to it. The publication parameter publish_via_partition_root is set as true. There are row filters defined on both the partitioned table (parent), and on the partition (child).
test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=true);
CREATE PUBLICATIONtest_sub=# CREATE SUBSCRIPTION s4
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
test_sub-# PUBLICATION p4;
CREATE SUBSCRIPTION直接向 parent 和 child 表中插入一些值。它们会使用 parent 的行筛选器复制（因为 publish_via_partition_root 为 true）。
Insert some values directly into the parent and child tables. They replicate using the row filter of parent (because publish_via_partition_root is true).
test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3
test_pub=# SELECT * FROM parent ORDER BY a;
a
---
2
3
4
5
6
7
(6 rows)test_sub=# SELECT * FROM parent ORDER BY a;
a
---
2
3
4
(3 rows)对同一测试重复执行操作，但是使用 publish_via_partition_root 的不同值。发布时间参数 publish_via_partition_root 设置为 false。在分区中定义了行筛选器（child）。
Repeat the same test, but with a different value for publish_via_partition_root. The publication parameter publish_via_partition_root is set as false. A row filter is defined on the partition (child).
test_pub=# DROP PUBLICATION p4;
DROP PUBLICATION
test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=false);
CREATE PUBLICATIONtest_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
ALTER SUBSCRIPTION在发布者上执行插入操作与之前相同。它们会使用 child 的行筛选器复制（因为 publish_via_partition_root 为 false）。
Do the inserts on the publisher same as before. They replicate using the row filter of child (because publish_via_partition_root is false).
test_pub=# TRUNCATE parent;
TRUNCATE TABLE
test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3
test_pub=# SELECT * FROM parent ORDER BY a;
a
---
2
3
4
5
6
7
(6 rows)test_sub=# SELECT * FROM child ORDER BY a;
a
---
5
6
7
(3 rows)