PostgreSQL 15：引入发布行过滤器

在这篇博文中，我将简要介绍 PostgreSQL 15发布行过滤器的新特性。此功能是由富士通 OSS 团队与 PostgreSQL 开源社区的其他成员密切合作添加的。

用户可能选择定义逻辑复制行过滤器的原因有多种，例如减少网络流量、减少噪音或消除不良数据、仅复制与订阅者相关的数据以及隐藏敏感信息。

目录导读

• 引言
• 功能概述
• 修改 CREATE PUBLICATION 语法
• 行过滤器表达式
• 应用行过滤器
• 组合多个行过滤器
• 初始表同步
• psql 命令的增强
• 影响
• 后记

引言

通常，当使用逻辑复制 PUB/SUB 时，发布表的所有数据更改都将复制到相应的订阅者。用户可以使用新的行过滤器功能限制复制，以便只有与过滤器表达式匹配的行数据被复制。

功能概述

在 CREATE PUBLICATION 时，可选择为每个表指定行过滤器。为每个要进行行过滤的表添加行过滤器 WHERE 子句。每个发布可能有零个或多个行过滤器。

修改 CREATE PUBLICATION 语法

这是一个简化的 CREATE PUBLICATION 语法，突出显示为 PostgreSQL 15 添加的新行过滤器 WHERE 子句：

CREATE PUBLICATION <pub-name> FOR TABLE <table-list>
<table-list> ::= <table-list-item> [, <table-list>]
<table-list-item> ::= <table-name> [ WHERE (<row-filter-expression>) ]

行过滤器表达式

行过滤器 WHERE 子句只允许简单的表达式，包括基本函数和逻辑运算符。它只能引用它所属的表的列。如果发布发布了任何 UPDATE 或 DELETE 操作，行过滤器表达式则必须仅包含表的“副本标识”所涵盖的列。如果发布仅发布 INSERT 操作，则行过滤器表达式可以使用任何列。

示例1

CREATE PUBLICATION cheap_widgets
FOR TABLE widget WHERE (price < 1.99);

示例2

CREATE PUBLICATION sales_people
FOR TABLE employees WHERE (role <> 'manager' AND dept = 'sales');

示例3

CREATE PUBLICATION rate_usa    FOR TABLE exchange_rates WHERE (country = 'us');
CREATE PUBLICATION rate_uk     FOR TABLE exchange_rates WHERE (country = 'uk');
CREATE PUBLICATION rate_france FOR TABLE exchange_rates WHERE (country = 'fr');

应用行过滤器

在决定发布更改之前应用行过滤器。如果行过滤器表达式的计算结果为 NULL 或 false，则不会复制该行。

• TRUNCATE TABLE 命令忽略行过滤器。

• 过滤的 INSERT 被复制为 INSERT。

• 过滤的 DELETE 被复制为 DELETE。

过滤的 UPDATE 更复杂，因为目标是订阅方复制的表数据必须最终遵守发布者上定义的行过滤器表达式。要决定实现这一点所需的操作意味着，无论何时处理 UPDATE，都必须针对旧行数据和新行数据（即 UPDATE 之前和之后）评估行过滤器表达式。

• 如果旧行不满足行过滤器表达式（我们断言该行在订阅服务器上不存在），但新行满足，那么为了数据一致性，应该将新行添加到订阅服务器——UPDATE 转换为插入。

• 如果旧行满足行过滤器表达式（我们断言该行已经发送给订阅者），但新行不满足，那么为了数据一致性，应该从订阅者中删除旧行 - UPDATE 转换为删除。

UPDATE 转变

组合多个行过滤器

有时订阅可能会订阅多个发布，其中同一个表是使用不同的行过滤器 WHERE 子句发布的。如果发生这种情况，那么这些表达式将被OR-ed在一起，以便复制满足任何表达式的行。

这也意味着如果表的订阅发布之一没有行过滤器，则该表的所有其他行过滤器将被忽略。

初始表同步

当用户选择使用（默认）copy_data = true订阅参数时，将仅复制满足发布的行过滤器的预先存在的数据。

psql 命令的增强

作为这一新功能的一部分，PostgreSQL 15 psql 命令也进行了修改，以提供有关行过滤器表达式的有用信息。

显示表命令 ( \d ) 现在在表所属的任何发布上显示 WHERE 子句。显示发布命令 ( \dRp+ ) 现在显示任何表的 WHERE 子句。

例子：

postgres=# CREATE TABLE data(id int, rgb text);
CREATE TABLE
postgres=# CREATE PUBLICATION pub_data_all FOR TABLE data;
CREATE PUBLICATION
postgres=# CREATE PUBLICATION pub_data_blue FOR TABLE data WHERE (rgb = 'B');
CREATE PUBLICATION
postgres=# CREATE PUBLICATION pub_data_red FOR TABLE data WHERE (rgb = 'R');
CREATE PUBLICATION
postgres=# \d data
                Table "public.data"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 rgb    | text    |           |          | 
Publications:
    "pub_data_all"
    "pub_data_blue" WHERE (rgb = 'B'::text)
    "pub_data_red" WHERE (rgb = 'R'::text)

postgres=# \dRp+
                          Publication pub_data_all
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.data"

                         Publication pub_data_blue
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.data" WHERE (rgb = 'B'::text)

                          Publication pub_data_red
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.data" WHERE (rgb = 'R'::text)

postgres=#

影响

用户可能选择定义逻辑复制行过滤器的原因有多种：

• 通过仅复制大型数据表的一小部分来减少网络流量（提高性能）。

例子：

CREATE PUBLICATION pub_data_2205 
FOR TABLE bank_transactions WHERE (year = 2022 AND month = 'May') ;

• 减少噪音或消除不良数据。

例子：

CREATE PUBLICATION pub_data_good 
FOR TABLE sensor WHERE (value IS NOT NULL AND value > 0) ;

• 仅提供与订阅者节点相关的数据。

例子：

CREATE PUBLICATION pub_data_new_york 
FOR TABLE weather WHERE (state = 'NY') ;

• 通过隐藏（而不是复制）敏感信息来充当一种安全形式。

例子：

CREATE PUBLICATION pub_data_salary 
FOR TABLE 员工WHERE (role <> 'manager') ;

后记

添加行过滤是 PostgreSQL 15 逻辑复制的主要功能。此功能为用户的工具箱提供了另一种工具，允许他们创建更复杂或定制的逻辑复制解决方案。富士通 OSS 团队将继续帮助增强和添加更多功能到 PostgreSQL 逻辑复制。

PostgreSQL 15 中也添加了与行过滤器相关的功能，即逻辑复制列列表。这将是我的一位同事即将发表的博客的主题。

原文地址：Introducing publication row filters
原文作者：Peter Smith