Horton

Thread

Commits

  1. Doc: Specify the interaction of publish_generated_columns with column list.

  1. DOCS - create publication (tweak for generated columns)

    Peter Smith <smithpb2250@gmail.com> — 2025-04-22T23:49:36Z

    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

  2. Re: DOCS - create publication (tweak for generated columns)

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-23T00:33:09Z

    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.

  3. Re: DOCS - create publication (tweak for generated columns)

    Peter Smith <smithpb2250@gmail.com> — 2025-04-28T02:38:00Z

    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

  4. Re: DOCS - create publication (tweak for generated columns)

    Amit Kapila <amit.kapila16@gmail.com> — 2025-04-28T09:51:37Z

    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.

  5. Re: DOCS - create publication (tweak for generated columns)

    Peter Smith <smithpb2250@gmail.com> — 2025-04-28T23:07:32Z

    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

  6. Re: DOCS - create publication (tweak for generated columns)

    Amit Kapila <amit.kapila16@gmail.com> — 2025-04-29T05:45:24Z

    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.