DOCS: Generated table columns are skipped by logical replication

On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote:

While reviewing another thread that proposes to include "generated
columns" support for logical replication [1] I was looking for any
existing PostgreSQL documentation on this topic.

But, I found almost nothing about it at all -- I only saw one aside
mention saying that logical replication low-level message information
is not sent for generated columns [2].

~~

IMO there should be some high-level place in the docs where the
behaviour for logical replication w.r.t. generated columns is
described.

+1.

There are lots of candidate places which could talk about this topic.
* e.g.1 in "Generated Columns" (section 5.4)
* e.g.2 in LR "Column-Lists" docs (section 29.5)
* e.g.3 in LR "Restrictions" docs (section 29.7)
* e.g.4 in the "CREATE PUBLICATION" reference page

For now, I have provided just a simple patch for the "Generated
Columns" section [3]. Perhaps it is enough.

Can we try to clarify if their corresponding values are replicated?

--
With Regards,
Amit Kapila.

On Tue, Jun 18, 2024 at 9:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote:

While reviewing another thread that proposes to include "generated
columns" support for logical replication [1] I was looking for any
existing PostgreSQL documentation on this topic.

But, I found almost nothing about it at all -- I only saw one aside
mention saying that logical replication low-level message information
is not sent for generated columns [2].

~~

IMO there should be some high-level place in the docs where the
behaviour for logical replication w.r.t. generated columns is
described.

+1.

There are lots of candidate places which could talk about this topic.
* e.g.1 in "Generated Columns" (section 5.4)
* e.g.2 in LR "Column-Lists" docs (section 29.5)
* e.g.3 in LR "Restrictions" docs (section 29.7)
* e.g.4 in the "CREATE PUBLICATION" reference page

For now, I have provided just a simple patch for the "Generated
Columns" section [3]. Perhaps it is enough.

Can we try to clarify if their corresponding values are replicated?

Sure. Here are some current PG17 observed behaviours demonstrating
that generated columns are not replicated.

======

Example #1

The generated cols 'b' column is not replicated. Notice the subscriber
side 'b' has its own computed value which uses a different
calculation.

PUB: create table t1 (a int, b int generated always as (a * 2) stored);
SUB: create table t1 (a int, b int generated always as (a * 20) stored);

PUB:
insert into t1 values (1),(2),(3);
create publication pub1 for table t1;
test_pub=# select * from t1;
a | b
---+---
1 | 2
2 | 4
3 | 6
(3 rows)

SUB:
create subscription sub1 connection 'dbname=test_pub' publication pub1;
test_sub=# select * from t1;
a | b
---+----
1 | 20
2 | 40
3 | 60
(3 rows)

======

Example 2

You cannot specify a generated column in a CREATE PUBLICATION column-list.

PUB:
create table t2 (a int, b int generated always as (a * 2) stored);
create publication pub2 for table t2(b);
ERROR: cannot use generated column "b" in publication column list

======

Example 3

Here the subscriber-side table doesn't even have a column 'b'.
Normally, a missing column like this would cause subscription errors,
but since the publisher-side generated column 'b' is not replicated,
this scenario is allowed.

PUB: create table t3 (a int, b int generated always as (a * 2) stored);
SUB: create table t3 (a int);

PUB:
create publication pub3 for table t3;
insert into t3 values (1),(2),(3);
test_pub=# select * from t3;
a | b
---+---
1 | 2
2 | 4
3 | 6
(3 rows)

SUB:
create subscription sub3 connection 'dbname=test_pub' publication pub3;
test_sub=# select * from t3;
a
---
1
2
3
(3 rows)

======
Kind Regards,
Peter Smith.
Fujitsu Australia

On Wed, Jun 19, 2024 at 6:46 AM Peter Smith <smithpb2250@gmail.com> wrote:

On Tue, Jun 18, 2024 at 9:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote:

While reviewing another thread that proposes to include "generated
columns" support for logical replication [1] I was looking for any
existing PostgreSQL documentation on this topic.

But, I found almost nothing about it at all -- I only saw one aside
mention saying that logical replication low-level message information
is not sent for generated columns [2].

~~

IMO there should be some high-level place in the docs where the
behaviour for logical replication w.r.t. generated columns is
described.

+1.

There are lots of candidate places which could talk about this topic.
* e.g.1 in "Generated Columns" (section 5.4)
* e.g.2 in LR "Column-Lists" docs (section 29.5)
* e.g.3 in LR "Restrictions" docs (section 29.7)
* e.g.4 in the "CREATE PUBLICATION" reference page

For now, I have provided just a simple patch for the "Generated
Columns" section [3]. Perhaps it is enough.

Can we try to clarify if their corresponding values are replicated?

Sure. Here are some current PG17 observed behaviours demonstrating
that generated columns are not replicated.

Thanks for sharing examples. Your proposed patch as-is looks good to
me. We should back-patch this unless you or someone else thinks
otherwise.

--
With Regards,
Amit Kapila.

On 18.06.24 08:40, Peter Smith wrote:

For now, I have provided just a simple patch for the "Generated
Columns" section [3]. Perhaps it is enough.

Makes sense.

+ Generated columns are skipped for logical replication, and cannot be
+ specified in a <command>CREATE PUBLICATION</command> column-list.

Maybe remove the comma, and change "column-list" to "column list".

On Wed, Jun 19, 2024 at 2:21 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

...

Thanks for sharing examples. Your proposed patch as-is looks good to
me. We should back-patch this unless you or someone else thinks
otherwise.

Hi Amit.

I modified the patch text slightly according to Peter E's suggestion [1]/messages/by-id/2b291af9-929f-49ab-b378-5cbc029d348f@eisentraut.org.

I also tested the above examples against all older PostgreSQL versions
12,13,14,15,16,17. The logical replication behaviour of skipping
generated columns is the same for all of them.

Note that CREATE PUBLICATION column lists did not exist until PG15, so
a modified patch is needed for the versions before that.

~

The attached "HEAD" patch is appropriate for HEAD, PG17, PG16, PG15
The attached "PG14" patch is appropriate for PG14, PG13, PG12

======
[1]: /messages/by-id/2b291af9-929f-49ab-b378-5cbc029d348f@eisentraut.org

Kind Regards,
Peter Smith.
Fujitsu Australia

On Thu, Jun 20, 2024 at 6:36 AM Peter Smith <smithpb2250@gmail.com> wrote:

Hi Amit.

I modified the patch text slightly according to Peter E's suggestion [1].

I also tested the above examples against all older PostgreSQL versions
12,13,14,15,16,17. The logical replication behaviour of skipping
generated columns is the same for all of them.

Note that CREATE PUBLICATION column lists did not exist until PG15, so
a modified patch is needed for the versions before that.

Pushed.

--
With Regards,
Amit Kapila.