DOCS - create publication (tweak for generated columns)
Hi,
While re-reading documentation about logical replication of generated
columns, I came across this paragraph in the CREATE PUBLICATION ...
FOR TABLE description [1]https://www.postgresql.org/docs/devel/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-TABLE.
----
When a column list is specified, only the named columns are
replicated. The column list can contain stored generated columns as
well. If no column list is specified, all table columns (except
generated columns) are replicated through this publication, including
any columns added later. It has no effect on TRUNCATE commands. See
Section 29.5 for details about column lists.
----
That "except generated columns" part is not strictly correct because
it fails to account for the 'publish_generated_columns' parameter.
I've suggested a more accurate description below.
SUGGESTION
----
When a column list is specified, only the named columns are
replicated. Stored generated columns may be included in the list.
Specifying a column list has no effect on TRUNCATE commands. See
Section 29.5 for details about column lists. If no column list is
specified, all table columns are replicated through this publication,
including any columns added later. Generated columns are included in
this case only if publish_generated_columns is set to stored.
----
I've attached a patch to implement this suggested wording.
Thoughts?
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v1-0001-DOCS-create-publication-for-table.patchapplication/octet-stream; name=v1-0001-DOCS-create-publication-for-table.patchDownload
From 6073a6a016608b08a65abfcc73bf2a3370e6411b Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Wed, 23 Apr 2025 09:30:37 +1000
Subject: [PATCH v1] DOCS - create publication for table
---
doc/src/sgml/ref/create_publication.sgml | 12 +++++++-----
1 file changed, 7 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 73f0c8d..f295dd1 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -89,12 +89,14 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
When a column list is specified, only the named columns are replicated.
- The column list can contain stored generated columns as well. If no
- column list is specified, all table columns (except generated columns)
- are replicated through this publication, including any columns added
- later. It has no effect on <literal>TRUNCATE</literal> commands. See
+ Stored generated columns may be included in the list. Specifying a column
+ list has no effect on <literal>TRUNCATE</literal> commands. See
<xref linkend="logical-replication-col-lists"/> for details about column
- lists.
+ lists. If no column list is specified, all table columns are replicated
+ through this publication, including any columns added later. Generated
+ columns are included in this case only if
+ <literal>publish_generated_columns</literal> is set to
+ <literal>stored</literal>.
</para>
<para>
--
1.8.3.1
On Tuesday, April 22, 2025, Peter Smith <smithpb2250@gmail.com> wrote:
Hi,
While re-reading documentation about logical replication of generated
columns, I came across this paragraph in the CREATE PUBLICATION ...
FOR TABLE description [1].----
When a column list is specified, only the named columns are
replicated. The column list can contain stored generated columns as
well. If no column list is specified, all table columns (except
generated columns) are replicated through this publication, including
any columns added later. It has no effect on TRUNCATE commands. See
Section 29.5 for details about column lists.
----That "except generated columns" part is not strictly correct because
it fails to account for the 'publish_generated_columns' parameter.
I've suggested a more accurate description below.SUGGESTION
----
When a column list is specified, only the named columns are
replicated. Stored generated columns may be included in the list.
Specifying a column list has no effect on TRUNCATE commands. See
Section 29.5 for details about column lists. If no column list is
specified, all table columns are replicated through this publication,
including any columns added later. Generated columns are included in
this case only if publish_generated_columns is set to stored.
----I've attached a patch to implement this suggested wording.
Thoughts?
======
[1] https://www.postgresql.org/docs/devel/sql-createpublication.html#SQL-
CREATEPUBLICATION-PARAMS-FOR-TABLE
In the column list I would stick to mentioning what cannot be specified,
since it would be assumed by default that any column on the table is fair
game. I believe that means not mentioning stored generated and instead
mentioning virtual generated. It really seems odds mentioning stored
generated being allowed before mentioning when they are optional, default
excluded.
Alternative:
The optional column list may include any non-virtual columns of the table.
If omitted, tables publish all (including future) non-generated columns by
default, and may publish stored generated columns if the publication option
publish_generated_columns is set to stored. See Section 29.5 for details
about column lists.
I don’t get why truncate is mentioned here. I omitted it intentionally.
David J.
On Wed, Apr 23, 2025 at 10:33 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
...
In the column list I would stick to mentioning what cannot be specified, since it would be assumed by default that any column on the table is fair game. I believe that means not mentioning stored generated and instead mentioning virtual generated. It really seems odds mentioning stored generated being allowed before mentioning when they are optional, default excluded.
Alternative:
The optional column list may include any non-virtual columns of the table. If omitted, tables publish all (including future) non-generated columns by default, and may publish stored generated columns if the publication option publish_generated_columns is set to stored. See Section 29.5 for details about column lists.
I don’t get why truncate is mentioned here. I omitted it intentionally.
Hi David,
Thanks for your feedback/suggestions. Sorry for my late reply -- I was
waiting for any other feedback, but none was forthcoming, so here is
my patch v2.
I've rearranged the text and mentioned non-virtual and non-generated
as suggested.
I also did not really understand why TRUNCATE is mentioned in this
paragraph, but I have left that as-is for now. For this thread, I only
wanted to address the generated column issue. I think any removal of
the TRUNCATE note ought to be handled separately, where more effort
can be spent to understand why it was put there in the first place.
Anyway, PSA patch v2.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v2-0001-DOCS-create-publication-for-table.patchapplication/octet-stream; name=v2-0001-DOCS-create-publication-for-table.patchDownload
From 44b87b3898ce84f0b3382806a29405f93e985a31 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 28 Apr 2025 12:27:33 +1000
Subject: [PATCH v2] DOCS - create publication for table
---
doc/src/sgml/ref/create_publication.sgml | 10 ++++++----
1 file changed, 6 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 73f0c8d..d75ff04 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -89,10 +89,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
When a column list is specified, only the named columns are replicated.
- The column list can contain stored generated columns as well. If no
- column list is specified, all table columns (except generated columns)
- are replicated through this publication, including any columns added
- later. It has no effect on <literal>TRUNCATE</literal> commands. See
+ The column list may include any non-virtual columns of the table. If the
+ column list is omitted, the publication will replicate all non-generated
+ columns (including any added in the future) by default. Stored generated
+ columns can also be replicated if <literal>publish_generated_columns</literal>
+ is set to <literal>stored</literal>. Specifying a column list has no
+ effect on <literal>TRUNCATE</literal> commands. See
<xref linkend="logical-replication-col-lists"/> for details about column
lists.
</para>
--
1.8.3.1
On Mon, Apr 28, 2025 at 8:08 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Wed, Apr 23, 2025 at 10:33 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
...In the column list I would stick to mentioning what cannot be specified, since it would be assumed by default that any column on the table is fair game. I believe that means not mentioning stored generated and instead mentioning virtual generated. It really seems odds mentioning stored generated being allowed before mentioning when they are optional, default excluded.
Alternative:
The optional column list may include any non-virtual columns of the table. If omitted, tables publish all (including future) non-generated columns by default, and may publish stored generated columns if the publication option publish_generated_columns is set to stored. See Section 29.5 for details about column lists.
I don’t get why truncate is mentioned here. I omitted it intentionally.
Hi David,
Thanks for your feedback/suggestions. Sorry for my late reply -- I was
waiting for any other feedback, but none was forthcoming, so here is
my patch v2.I've rearranged the text and mentioned non-virtual and non-generated
as suggested.I also did not really understand why TRUNCATE is mentioned in this
paragraph, but I have left that as-is for now.
I think TRUNCATE is mentioned because column_list has an impact on all
other DML commands like insert, update, delete that gets replicated
except Truncate. So, leaving Truncate as it is seems okay to me.
- The column list can contain stored generated columns as well. If no
- column list is specified, all table columns (except generated columns)
- are replicated through this publication, including any columns added
- later. It has no effect on <literal>TRUNCATE</literal> commands. See
+ The column list may include any non-virtual columns of the table. If the
+ column list is omitted, the publication will replicate all non-generated
+ columns (including any added in the future) by default. Stored generated
+ columns can also be replicated if
<literal>publish_generated_columns</literal>
+ is set to <literal>stored</literal>.I find the use of non-virtual in the first line and non-generated in
the second line a bit confusing. Can we keep the first line as it is
(The column list can contain stored generated columns as well.)?
--
With Regards,
Amit Kapila.
On Mon, Apr 28, 2025 at 7:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Apr 28, 2025 at 8:08 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Wed, Apr 23, 2025 at 10:33 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
...In the column list I would stick to mentioning what cannot be specified, since it would be assumed by default that any column on the table is fair game. I believe that means not mentioning stored generated and instead mentioning virtual generated. It really seems odds mentioning stored generated being allowed before mentioning when they are optional, default excluded.
Alternative:
The optional column list may include any non-virtual columns of the table. If omitted, tables publish all (including future) non-generated columns by default, and may publish stored generated columns if the publication option publish_generated_columns is set to stored. See Section 29.5 for details about column lists.
I don’t get why truncate is mentioned here. I omitted it intentionally.
Hi David,
Thanks for your feedback/suggestions. Sorry for my late reply -- I was
waiting for any other feedback, but none was forthcoming, so here is
my patch v2.I've rearranged the text and mentioned non-virtual and non-generated
as suggested.I also did not really understand why TRUNCATE is mentioned in this
paragraph, but I have left that as-is for now.I think TRUNCATE is mentioned because column_list has an impact on all
other DML commands like insert, update, delete that gets replicated
except Truncate. So, leaving Truncate as it is seems okay to me.- The column list can contain stored generated columns as well. If no - column list is specified, all table columns (except generated columns) - are replicated through this publication, including any columns added - later. It has no effect on <literal>TRUNCATE</literal> commands. See + The column list may include any non-virtual columns of the table. If the + column list is omitted, the publication will replicate all non-generated + columns (including any added in the future) by default. Stored generated + columns can also be replicated if <literal>publish_generated_columns</literal> + is set to <literal>stored</literal>.I find the use of non-virtual in the first line and non-generated in
the second line a bit confusing. Can we keep the first line as it is
(The column list can contain stored generated columns as well.)?
Hi Amit,
Please find patch v3 with the paragraph's 2nd sentence restored as requested.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v3-0001-DOCS-create-publication-for-table.patchapplication/octet-stream; name=v3-0001-DOCS-create-publication-for-table.patchDownload
From bb8ac00464d572dc02a1bb5306338332cd3e9e4f Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 29 Apr 2025 08:52:48 +1000
Subject: [PATCH v3] DOCS - create publication for table
---
doc/src/sgml/ref/create_publication.sgml | 10 ++++++----
1 file changed, 6 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 73f0c8d..802630f 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -89,10 +89,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
When a column list is specified, only the named columns are replicated.
- The column list can contain stored generated columns as well. If no
- column list is specified, all table columns (except generated columns)
- are replicated through this publication, including any columns added
- later. It has no effect on <literal>TRUNCATE</literal> commands. See
+ The column list can contain stored generated columns as well. If the
+ column list is omitted, the publication will replicate all non-generated
+ columns (including any added in the future) by default. Stored generated
+ columns can also be replicated if <literal>publish_generated_columns</literal>
+ is set to <literal>stored</literal>. Specifying a column list has no
+ effect on <literal>TRUNCATE</literal> commands. See
<xref linkend="logical-replication-col-lists"/> for details about column
lists.
</para>
--
1.8.3.1