Re: DOCS: Generated table columns are skipped by logical replication

On Tue, Jun 18, 2024 at 9:40 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250(at)gmail(dot)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