Thread: 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].

----
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

Kind Regards,
Peter Smith.
Fujitsu Australia

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

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
anycolumn on the table is fair game.  I believe that means not mentioning stored generated and instead mentioning
virtualgenerated.  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_columnsis 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

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
anycolumn on the table is fair game.  I believe that means not mentioning stored generated and instead mentioning
virtualgenerated.  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
(includingfuture) non-generated columns by default, and may publish stored generated columns if the publication option
publish_generated_columnsis 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
thatany column on the table is fair game.  I believe that means not mentioning stored generated and instead mentioning
virtualgenerated.  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
(includingfuture) non-generated columns by default, and may publish stored generated columns if the publication option
publish_generated_columnsis 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

On Tue, Apr 29, 2025 at 4:38 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Please find patch v3 with the paragraph's 2nd sentence restored as requested.
>

Thanks, Pushed!

--
With Regards,
Amit Kapila.