From 5d9adf3adc3346636485dde701f673f071329d7b Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Mon, 13 Jan 2025 09:08:59 +1100 Subject: [PATCH v51] 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 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 | 299 +++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_publication.sgml | 5 + 3 files changed, 305 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 8290cd1..ffba2c2 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1406,6 +1406,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. @@ -1570,6 +1578,297 @@ 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 generated columns just like regular ones. + + + There are two ways to do this: + + + + Enable the PUBLICATION parameter + + publish_generated_columns. This instructs + PostgreSQL logical replication to publish current and future generated + columns of the publication's tables. + + + + + Specify a table column list + to explicity nominate which 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 disabled (default), and for when it is + enabled. + + + Replication Result Summary + + + + Publish generated columns?Publisher table columnSubscriber table columnResult + + + + + NoGENERATEDGENERATEDPublisher table column is not replicated. Use the subscriber table generated column value. + + + NoGENERATEDregularPublisher table column is not replicated. Use the subscriber table regular column default value. + + + NoGENERATED--missing--Publisher table column is not replicated. Nothing happens. + + + YesGENERATEDGENERATEDERROR. Not supported. + + + YesGENERATEDregularPublisher table column value is replicated to the subscriber table column. + + + YesGENERATED--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, +test_pub(# c int GENERATED ALWAYS AS (a + 1) STORED, +test_pub(# d int GENERATED ALWAYS AS (b + 1) STORED); +CREATE TABLE + +test_pub=# CREATE TABLE t2 (a int PRIMARY KEY, b int, +test_pub(# c int GENERATED ALWAYS AS (a + 1) STORED, +test_pub(# d int GENERATED ALWAYS AS (b + 1) STORED); +CREATE TABLE + + +test_sub=# CREATE TABLE t1 (a int PRIMARY KEY, b int, +test_sub(# c int, +test_sub(# d int GENERATED ALWAYS AS (b * 100) STORED); +CREATE TABLE + +test_sub=# CREATE TABLE t2 (a int PRIMARY KEY, b int, +test_sub(# c int, +test_sub(# d int); +CREATE TABLE + + + + 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=false, + but that is just for demonstration because false is the + default anyway. + +test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1, t2(a,c) +test_pub-# WITH (publish_generated_columns=false); +CREATE PUBLICATION + + +test_sub=# CREATE SUBSCRIPTION sub1 +test_sub-# CONNECTION 'dbname=test_pub' +test_sub-# PUBLICATION pub1; +CREATE SUBSCRIPTION + + + + 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=false. The subscriber + t2.c default column value is used. + + + t1.d is a generated column. It is not replicated because + publish_generated_columns=false. 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 5e25536..f073c78 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -206,6 +206,11 @@ CREATE PUBLICATION name publisher. + + + See for more details about + logical replication of generated columns. + -- 1.8.3.1