From 0bbae7f241b36a81432cb438019d697ad6d46d70 Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Thu, 23 Jan 2025 14:36:26 +1100 Subject: [PATCH v56] DOCS - Generated Column Replication. This patch adds a new section "Generated Column Replication" to the "Logical Replication" documentation chapter. Author: Peter Smith Reviewed By: Vignesh C, Peter Eisentraut Discussion: https://www.postgresql.org/message-id/flat/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E%40gmail.com --- doc/src/sgml/ddl.sgml | 1 + doc/src/sgml/logical-replication.sgml | 346 +++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_publication.sgml | 5 + 3 files changed, 352 insertions(+) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index dea04d6..7ff39ae 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -519,6 +519,7 @@ CREATE TABLE people ( publish_generated_columns or by including them in the column list of the CREATE PUBLICATION command. + See for details. diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ab683cf..d5283f5 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1430,6 +1430,14 @@ test_sub=# SELECT * FROM child ORDER BY a; + Generated columns can also be specified in a column list. This allows + generated columns to be published, regardless of the publication parameter + + publish_generated_columns. See + for details. + + + Specifying a column list when the publication also publishes FOR TABLES IN SCHEMA is not supported. @@ -1594,6 +1602,344 @@ test_sub=# SELECT * FROM t1 ORDER BY id; + + Generated Column Replication + + + Typically, a table at the subscriber will be defined the same as the + publisher table, so if the publisher table has a + GENERATED column then the subscriber table will + have a matching generated column. In this case, it is always the subscriber + table generated column value that is used. + + + + For example, note below that subscriber table generated column value comes from the + subscriber column's calculation. + +test_pub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED); +CREATE TABLE +test_pub=# INSERT INTO tab_gen_to_gen VALUES (1),(2),(3); +INSERT 0 3 +test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen; +CREATE PUBLICATION +test_pub=# SELECT * FROM tab_gen_to_gen; + a | b +---+--- + 1 | 2 + 2 | 3 + 3 | 4 +(3 rows) + +test_sub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED); +CREATE TABLE +test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1; +CREATE SUBSCRIPTION +test_sub=# SELECT * from tab_gen_to_gen; + a | b +---+---- + 1 | 100 + 2 | 200 + 3 | 300 +(3 rows) + + + + + In fact, prior to version 18.0, logical replication does not publish + GENERATED columns at all. + + + + But, replicating a generated column to a regular column can sometimes be + desirable. + + + This feature may be useful when replicating data to a + non-PostgreSQL database via plugin output, especially if the target database + does not support generated columns. + + + + + + How to Publish Generated Columns + + + Generated columns are not published by default, but users can opt to + publish stored generated columns just like regular ones. + + + + There are two ways to do this: + + + + Set the PUBLICATION parameter + + publish_generated_columns to stored. + This instructs PostgreSQL logical replication to publish current and + future stored generated columns of the publication's tables. + + + + + + Specify a table column list + to explicitly nominate which stored generated columns will be published. + + + + + When determining which table columns will be published, a column list + takes precedence, overriding the effect of the + publish_generated_columns parameter. + + + + + + + + + Behavior Summary + + + The following table summarizes behavior when there are generated columns + involved in the logical replication. Results are shown for when + publishing generated columns is not enabled, and for when it is + enabled. + + + + Replication Result Summary + + + + + Publish generated columns? + Publisher table column + Subscriber table column + Result + + + + + + No + GENERATED + GENERATED + Publisher table column is not replicated. Use the subscriber table generated column value. + + + + No + GENERATED + regular + Publisher table column is not replicated. Use the subscriber table regular column default value. + + + + No + GENERATED + --missing-- + Publisher table column is not replicated. Nothing happens. + + + + Yes + GENERATED + GENERATED + ERROR. Not supported. + + + + Yes + GENERATED + regular + Publisher table column value is replicated to the subscriber table column. + + + + Yes + GENERATED + --missing-- + ERROR. The column is reported as missing from the subscriber table. + + + +
+ + + + There's currently no support for subscriptions comprising several + publications where the same table has been published with different column + lists. See . + + + + This same situation can occur if one publication is publishing generated + columns, while another publication in the same subscription is not + publishing generated columns for the same table. + + + + + + If the subscriber is from a release prior to 18, then initial table + synchronization won't copy generated columns even if they are defined in + the publisher. + + + + + + Examples + + + Setup the publisher and subscriber tables. Note that the subscriber + table columns have same names, but are not defined the same as the + publisher columns. + +test_pub=# CREATE TABLE t1 ( + a int PRIMARY KEY, + b int, + c int GENERATED ALWAYS AS (a + 1) STORED, + d int GENERATED ALWAYS AS (b + 1) STORED); + +test_pub=# CREATE TABLE t2 ( + a int PRIMARY KEY, + b int, + c int GENERATED ALWAYS AS (a + 1) STORED, + d int GENERATED ALWAYS AS (b + 1) STORED); + + + +test_sub=# CREATE TABLE t1 ( + a int PRIMARY KEY, + b int, + c int, + d int GENERATED ALWAYS AS (b * 100) STORED); + +test_sub=# CREATE TABLE t2 ( + a int PRIMARY KEY, + b int, + c int, + d int); + + + + + Create the PUBLICATION and the SUBSCRIPTION. + Note that the publication specifies a column list for table t2. + The publication also sets parameter publish_generated_columns=none, + but that is just for demonstration because none is the + default anyway. + +test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1, t2(a,c) + WITH (publish_generated_columns=none); + + + +test_sub=# CREATE SUBSCRIPTION sub1 + CONNECTION 'dbname=test_pub' + PUBLICATION pub1; + + + + + Insert some data to the publisher tables: + +test_pub=# INSERT INTO t1 VALUES (1,2); +INSERT 0 1 +test_pub=# INSERT INTO t2 VALUES (1,2); +INSERT 0 1 + +test_pub=# SELECT * FROM t1; + a | b | c | d +---+---+---+--- + 1 | 2 | 2 | 3 +(1 row) + +test_pub=# SELECT * FROM t2; + a | b | c | d +---+---+---+--- + 1 | 2 | 2 | 3 +(1 row) + + + + + Observe how columns for table t1 were replicated: + +test_sub=# SELECT * FROM t1; + a | b | c | d +---+---+---+----- + 1 | 2 | | 200 +(1 row) + + + + t1.a is a regular column. It gets replicated normally. + + + + t1.b is a regular column. It gets replicated normally. + + + + t1.c is a generated column. It is not replicated because + publish_generated_columns=none. The subscriber + t2.c default column value is used. + + + + t1.d is a generated column. It is not replicated because + publish_generated_columns=none. The subscriber + t2.d generated column value is used. + + + + + + Observe how columns for table t2 were replicated. + +test_sub=# SELECT * FROM t2; + a | b | c | d +---+---+---+--- + 1 | | 2 | +(1 row) + + + + t2.a is a regular column. It was specified in the column + list, so is replicated normally. + + + + t2.b is a regular column. It was not specified in column + list so is not replicated. The subscriber t2.b default + value is used. + + + + t2.c is a generated column. It was specified in the + column list, so is replicated to the subscriber t2.c + regular column. + + + + t2.d is a generated column. It was not specified in the + column list, so is not replicated. The subscriber t2.d + default value is used. + + + + + + + + Conflicts diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index e822ea2..73f0c8d 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -217,6 +217,11 @@ CREATE PUBLICATION name in the publisher. + + + See for more details about + logical replication of generated columns. + -- 1.8.3.1