From cbc4d48fceba5a188cd034c90c83305cfd12590c Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Thu, 30 Jan 2025 09:11:01 +1100 Subject: [PATCH v58] DOCS - Generated Column Replication. This patch adds a new section "Generated Column Replication" to the "Logical Replication" documentation chapter. The "Examples" sub-section is not included in this patch. Author: Peter Smith Reviewed By: Vignesh C, Peter Eisentraut, Amit Kapila 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 | 192 +++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_publication.sgml | 5 + 3 files changed, 198 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 07a07df..613abcd 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,190 @@ 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 output plugin, especially if the target database + does not support 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. + + + + + + + + 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. + + + + 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