Postgresql 中文操作指南

31.4. Column Lists #

每个发布可以根据需要指定哪些表的哪些列复制到订阅者。订阅者一侧的表必须至少具有已发布的所有列。如果未指定列列表，则复制发布者上的所有列。有关语法的详细信息，请参见 CREATE PUBLICATION 。

Each publication can optionally specify which columns of each table are replicated to subscribers. The table on the subscriber side must have at least all the columns that are published. If no column list is specified, then all columns on the publisher are replicated. See CREATE PUBLICATION for details on the syntax.

根据行为或性能原因，可以选择列。但是，不要依赖此功能实现安全性：恶意订阅者可以获取未具体发布的列中的数据。如果需要考虑安全性，则保护可以在发布者端应用。

The choice of columns can be based on behavioral or performance reasons. However, do not rely on this feature for security: a malicious subscriber is able to obtain data from columns that are not specifically published. If security is a consideration, protections can be applied at the publisher side.

如果未指定列列表，则后来添加的任何列都会自动传播。这意味着具有包含所有列的列列表与根本没有列列表并不相同。

If no column list is specified, any columns added later are automatically replicated. This means that having a column list which names all columns is not the same as having no column list at all.

列列表中只能包含简单的列引用。列表中各列的顺序不会保留。

A column list can contain only simple column references. The order of columns in the list is not preserved.

如果发布也发布 FOR TABLES IN SCHEMA ，则不支持指定列列表。

Specifying a column list when the publication also publishes FOR TABLES IN SCHEMA is not supported.

对于分区表，发布参数 publish_via_partition_root 确定使用哪一个列列表。如果 publish_via_partition_root 为 true ，将使用根分区表的列列表。否则，如果 publish_via_partition_root 为 false （默认），将使用每个分区的列列表。

For partitioned tables, the publication parameter publish_via_partition_root determines which column list is used. If publish_via_partition_root is true, the root partitioned table’s column list is used. Otherwise, if publish_via_partition_root is false (the default), each partition’s column list is used.

如果某篇发布发布了 UPDATE 或 DELETE 操作，任何列列表都必须包含表的复制标识列（请参阅 REPLICA IDENTITY ）。如果一篇发布仅发布 INSERT 操作，则列列表可以省略复制标识列。

If a publication publishes UPDATE or DELETE operations, any column list must include the table’s replica identity columns (see REPLICA IDENTITY). If a publication publishes only INSERT operations, then the column list may omit replica identity columns.

列列表对 TRUNCATE 命令无效。

Column lists have no effect for the TRUNCATE command.

在初始数据同步期间，仅复制已发布的列。但是，如果订阅者来自版本 15 之前，则所有表列在初始数据同步期间被复制，忽略所有列列表。

During initial data synchronization, only the published columns are copied. However, if the subscriber is from a release prior to 15, then all the columns in the table are copied during initial data synchronization, ignoring any column lists.

Warning: Combining Column Lists from Multiple Publications

当前不支持包含多篇发布的订阅，其中相同的表已发布到不同的列列表中。 CREATE SUBSCRIPTION 不允许创建这样的订阅，但仍有可能在订阅创建之后通过添加或更改发布端的列列表而进入该情况。

There’s currently no support for subscriptions comprising several publications where the same table has been published with different column lists. CREATE SUBSCRIPTION disallows creating such subscriptions, but it is still possible to get into that situation by adding or altering column lists on the publication side after a subscription has been created.

这意味着在已经订阅的分布的表中更改列列表可导致在订阅者侧抛出错误。

This means changing the column lists of tables on publications that are already subscribed could lead to errors being thrown on the subscriber side.

如果订阅受到此问题的影响，恢复复制的唯一方式是在发布方调整其中一个列列表，以使它们全部匹配；然后重新创建订阅，或使用 ALTER SUBSCRIPTION … DROP PUBLICATION 删除其中一个出错的分布，并再次添加它。

If a subscription is affected by this problem, the only way to resume replication is to adjust one of the column lists on the publication side so that they all match; and then either recreate the subscription, or use ALTER SUBSCRIPTION … DROP PUBLICATION to remove one of the offending publications and add it again.

31.4.1. Examples #

创建表格 t1 以在以下示例中使用。

Create a table t1 to be used in the following example.

test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
CREATE TABLE

创建分布 p1。定义表格 t1 的列列表，以减少需要复制的列数。请注意，列列表中列名称的顺序无关紧要。

Create a publication p1. A column list is defined for table t1 to reduce the number of columns that will be replicated. Notice that the order of column names in the column list does not matter.

test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
CREATE PUBLICATION

可以 psql 用于显示每个分布（如果定义了）的列列表。

psql can be used to show the column lists (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" (id, a, b, d)

可以使用 psql 显示每张表格的列列表（如果已定义）。

psql can be used to show the column lists (if defined) for each table.

test_pub=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 a      | text    |           |          |
 b      | text    |           |          |
 c      | text    |           |          |
 d      | text    |           |          |
 e      | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Publications:
    "p1" (id, a, b, d)

在订阅者节点上，创建一个表格 t1，它现在只需要的发布者表格 t1 上的部分列；并且还创建订阅 s1，订阅分布 p1。

On the subscriber node, create a table t1 which now only needs a subset of the columns that were on the publisher table t1, and also create the subscription s1 that subscribes to the publication p1.

test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION

在发布者节点上，插入一些行到表格 t1。

On the publisher node, insert some rows to table t1.

test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
INSERT 0 1
test_pub=# SELECT * FROM t1 ORDER BY id;
 id |  a  |  b  |  c  |  d  |  e
----+-----+-----+-----+-----+-----
  1 | a-1 | b-1 | c-1 | d-1 | e-1
  2 | a-2 | b-2 | c-2 | d-2 | e-2
  3 | a-3 | b-3 | c-3 | d-3 | e-3
(3 rows)

仅复制来自分布 p1 的列列表的数据。

Only data from the column list of publication p1 is replicated.

test_sub=# SELECT * FROM t1 ORDER BY id;
 id |  b  |  a  |  d
----+-----+-----+-----
  1 | b-1 | a-1 | d-1
  2 | b-2 | a-2 | d-2
  3 | b-3 | a-3 | d-3
(3 rows)