From f2af9172f7bcf65f6b86ce7a94459e46d971c969 Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Thu, 10 Oct 2024 11:19:22 +1100 Subject: [PATCH v40 2/3] DOCS - Generated Column Replication. This patch updates docs to describe the new feature allowing replication of generated columns. This includes addition of 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 | 4 +- doc/src/sgml/logical-replication.sgml | 290 +++++++++++++++++++++++ doc/src/sgml/protocol.sgml | 4 +- doc/src/sgml/ref/create_publication.sgml | 16 ++ 4 files changed, 310 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8ab0ddb112..192180d658 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -514,8 +514,8 @@ CREATE TABLE people ( - Generated columns are skipped for logical replication and cannot be - specified in a CREATE PUBLICATION column list. + Generated columns are not always published during logical replication. See + for details. diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 98a7ad0c27..7a8524e825 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1404,6 +1404,14 @@ test_sub=# SELECT * FROM child ORDER BY a; of columns in the list is not preserved. + + 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 @@ -1567,6 +1575,288 @@ 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. + + + + + + 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/protocol.sgml b/doc/src/sgml/protocol.sgml index 057c46f3f5..71b6b2a535 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -6544,7 +6544,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" Next, the following message part appears for each column included in - the publication (except generated columns): + the publication: @@ -7477,7 +7477,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" - Next, one of the following submessages appears for each column (except generated columns): + Next, one of the following submessages appears for each column: diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index fd9c5deac9..c13cd4db74 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -222,6 +222,22 @@ CREATE PUBLICATION name + + + publish_generated_columns (boolean) + + + Specifies whether the generated columns present in the tables + associated with the publication should be replicated. + The default is false. + + + See for more details about + logical replication of generated columns. + + + + -- 2.34.1