DOCS - Generated Column Replication Examples
Hi,
A recent commit [1]https://github.com/postgres/postgres/commit/6252b1eaf82bb8db361341d1c8651e43b29be816#diff-e593b875ae1b49848e9954e7a3ea26fb34de7454b338a0029aec72b0fb25bb0a added a new section "29.6. Generated Column
Replication" to the documentation [2]https://www.postgresql.org/docs/devel/logical-replication-gencols.html. But, no "Examples" were
included.
I've created this new thread to make a case for adding an "Examples"
subsection to that page.
(the proposed examples patch is attached)
======
1. Reasons AGAINST Adding Examples to this section (and why I disagree):
1a. The necessary information to describe the feature can already be
found in the text.
Yes, the documentation describes the feature, as it should. However,
examples serve a different purpose: they reinforce understanding by
demonstrating how the feature works in practice. They don’t introduce
new details; they clarify and illustrate existing ones.
1b. I can understand the feature without needing examples.
PostgreSQL serves a diverse user base with different levels of
expertise and learning styles. While experienced users might grasp the
feature quickly, others may struggle with dense explanatory text and
behaviour matrices. Why knowingly make it harder for them? If examples
can help some users, and others can simply skip them, there's no
downside to including them.
1c. Too many examples will swamp the page.
Agreed—too many examples would be excessive. That’s why the proposed
patch includes only a couple of well-chosen cases, rather than
covering every possible combination. This keeps the documentation
focused and useful without overwhelming the reader.
~~~
2. Reasons FOR Adding Examples to this section:
2a. To emphasise some special and/or subtle rules.
Some critical details—like how publication column lists override the
publish_generated_columns setting—are easy to overlook in a dense set
of behaviour rules. An example makes this clear by demonstrating the
effect directly, ensuring users don't miss it.
2b. To make it easy for users to try it themselves.
These examples are self-contained including the necessary
publication/subscription and tables. A user can simply cut/paste these
to PSQL and begin experimenting with the feature immediately, instead
of piecing together a working setup from scattered explanations. This
reduces friction and encourages hands-on learning.
2c. It may become inevitable later anyway.
With the eventual introduction of VIRTUAL generated columns,
replication behaviour will become even more complex, requiring more
rules and expanded behaviour matrices. Including examples now lays the
groundwork for future enhancements
~~~
Thoughts?
======
[1]: https://github.com/postgres/postgres/commit/6252b1eaf82bb8db361341d1c8651e43b29be816#diff-e593b875ae1b49848e9954e7a3ea26fb34de7454b338a0029aec72b0fb25bb0a
[2]: https://www.postgresql.org/docs/devel/logical-replication-gencols.html
Kind Regards,
Peter Smith.
Fujitsu Australia.
Attachments:
v1-0001-DOCS-Generated-Column-Replication-Examples.patchapplication/octet-stream; name=v1-0001-DOCS-Generated-Column-Replication-Examples.patchDownload
From e37431e477c8a4f193cb49083c5095143ec3316e Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 3 Feb 2025 09:46:10 +1100
Subject: [PATCH v1] DOCS - Generated Column Replication Examples
---
doc/src/sgml/logical-replication.sgml | 146 ++++++++++++++++++++++++++++++++++
1 file changed, 146 insertions(+)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 613abcd..fd4aa43 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1784,6 +1784,152 @@ test_sub=# SELECT * from tab_gen_to_gen;
the publisher.
</para>
</note>
+
+ <sect2 id="logical-replication-gencols-examples">
+ <title>Examples</title>
+
+ <para>
+ 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.
+<programlisting>
+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);
+</programlisting>
+
+<programlisting>
+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);
+</programlisting>
+ </para>
+
+ <para>
+ Create the <literal>PUBLICATION</literal> and the <literal>SUBSCRIPTION</literal>.
+ Note that the publication specifies a column list for table <literal>t2</literal>.
+ The publication also sets parameter <literal>publish_generated_columns=none</literal>,
+ but that is just for demonstration because <literal>none</literal> is the
+ default anyway.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1, t2(a,c)
+ WITH (publish_generated_columns=none);
+</programlisting>
+
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+ CONNECTION 'dbname=test_pub'
+ PUBLICATION pub1;
+</programlisting>
+ </para>
+
+ <para>
+ Insert some data to the publisher tables:
+<programlisting>
+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)
+</programlisting>
+ </para>
+
+ <para>
+ Observe how columns for table <literal>t1</literal> were replicated:
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a | b | c | d
+---+---+---+-----
+ 1 | 2 | | 200
+(1 row)
+</programlisting>
+ <itemizedlist>
+ <listitem><para>
+ <literal>t1.a</literal> is a regular column. It gets replicated normally.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t1.b</literal> is a regular column. It gets replicated normally.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t1.c</literal> is a generated column. It is not replicated because
+ <literal>publish_generated_columns=none</literal>. The subscriber
+ <literal>t2.c</literal> default column value is used.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t1.d</literal> is a generated column. It is not replicated because
+ <literal>publish_generated_columns=none</literal>. The subscriber
+ <literal>t2.d</literal> generated column value is used.
+ </para></listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Observe how columns for table <literal>t2</literal> were replicated.
+<programlisting>
+test_sub=# SELECT * FROM t2;
+ a | b | c | d
+---+---+---+---
+ 1 | | 2 |
+(1 row)
+</programlisting>
+ <itemizedlist>
+ <listitem><para>
+ <literal>t2.a</literal> is a regular column. It was specified in the column
+ list, so is replicated normally.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t2.b</literal> is a regular column. It was not specified in column
+ list so is not replicated. The subscriber <literal>t2.b</literal> default
+ value is used.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t2.c</literal> is a generated column. It was specified in the
+ column list, so is replicated to the subscriber <literal>t2.c</literal>
+ regular column.
+ </para></listitem>
+
+ <listitem><para>
+ <literal>t2.d</literal> is a generated column. It was not specified in the
+ column list, so is not replicated. The subscriber <literal>t2.d</literal>
+ default value is used.
+ </para></listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
<sect1 id="logical-replication-conflicts">
--
1.8.3.1
On Mon, Feb 3, 2025 at 4:23 AM Peter Smith <smithpb2250@gmail.com> wrote:
A recent commit [1] added a new section "29.6. Generated Column
Replication" to the documentation [2]. But, no "Examples" were
included.I've created this new thread to make a case for adding an "Examples"
subsection to that page.(the proposed examples patch is attached)
======
1. Reasons AGAINST Adding Examples to this section (and why I disagree):
1a. The necessary information to describe the feature can already be
found in the text.
I still feel that the current text and example given on the page are
sufficient for now. This is a new feature, and I think it is better to
get some feedback from users. We can add more examples or information
based on real user feedback. Also, we haven't got any other response
on this thread yet. So, I think we can wait for a week or so more to
see if anyone else also thinks that the additional examples will be
useful; if not, then return this patch for now.
--
With Regards,
Amit Kapila.
On Sat, 1 Mar 2025 at 19:01, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Feb 3, 2025 at 4:23 AM Peter Smith <smithpb2250@gmail.com> wrote:
A recent commit [1] added a new section "29.6. Generated Column
Replication" to the documentation [2]. But, no "Examples" were
included.I've created this new thread to make a case for adding an "Examples"
subsection to that page.(the proposed examples patch is attached)
======
1. Reasons AGAINST Adding Examples to this section (and why I disagree):
1a. The necessary information to describe the feature can already be
found in the text.I still feel that the current text and example given on the page are
sufficient for now. This is a new feature, and I think it is better to
get some feedback from users. We can add more examples or information
based on real user feedback. Also, we haven't got any other response
on this thread yet. So, I think we can wait for a week or so more to
see if anyone else also thinks that the additional examples will be
useful; if not, then return this patch for now.
It seems there is currently little interest in this, so I’ve closed
the commitfest entry for now. We can revisit this later if we receive
feedback from users indicating it's needed.
Regards,
Vignesh