Re: Pgoutput not capturing the generated columns

On Wed, Nov 6, 2024 at 11:35 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> I am observing some unexpected errors with the following scenario.
>

You are getting an expected ERROR. It is because of the design of
logical decoding which relies on historic snapshots.

> ======
> Tables:
>
> Publisher table:
> test_pub=# create table t1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
> CREATE TABLE
> test_pub=# insert into t1 values (1);
> INSERT 0 1
>
> ~
>
> And Subscriber table:
> test_sub=# create table t1(a int, b int);
> CREATE TABLE
>
> ======
> TEST PART 1.
>
> I create 2 publications, having different parameter values.
>
> test_pub=# create publication pub1 for table t1 with
> (publish_generated_columns=true);
> CREATE PUBLICATION
> test_pub=# create publication pub2 for table t1 with
> (publish_generated_columns=false);
> CREATE PUBLICATION
>
> ~
>
> And I try creating a subscription simultaneously subscribing to both
> of these publications. This fails with an expected error.
>
> test_sub=# create subscription sub1 connection 'dbname=test_pub'
> publication pub1, pub2;
> ERROR:  cannot use different column lists for table "public.t1" in
> different publications
>
> ======
> TEST PART 2.
>
> Now on publisher set parameter for pub2 to be true;
>
> test_pub=# alter publication pub2 set (publish_generated_columns);
> ALTER PUBLICATION
> test_pub=# \dRp+
>                                         Publication pub1
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via
> root | Genera
> ted columns
> ----------+------------+---------+---------+---------+-----------+----------+-------
> ------------
>  postgres | f          | t       | t       | t       | t         | f        | t
> Tables:
>     "public.t1"
>
>                                         Publication pub2
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via
> root | Genera
> ted columns
> ----------+------------+---------+---------+---------+-----------+----------+-------
> ------------
>  postgres | f          | t       | t       | t       | t         | f        | t
> Tables:
>     "public.t1"
>
> ~
>
> Now the create subscriber works OK.
>
> test_sub=# create subscription sub1 connection 'dbname=test_pub'
> publication pub1,pub2;
> NOTICE:  created replication slot "sub1" on publisher
> CREATE SUBSCRIPTION
>
> ======
> TEST PART 3.
>
> Now on Publisher let's alter that parameter back to false again...
>
> test_pub=# alter publication pub2 set (publish_generated_columns=false);
> ALTER PUBLICATION
>
> And insert some data.
>
> test_pub=# insert into t1 values (2);
> INSERT 0 1
>
> ~
>
> Now the subscriber starts failing again...
>
> ERROR:  cannot use different values of publish_generated_columns for
> table "public.t1" in different publications
> etc...
>
> ======
> TEST PART 4.
>
> Finally, on the Publisher alter that parameter back to true again!
>
> test_pub=# alter publication pub2 set (publish_generated_columns);
> ALTER PUBLICATION
...
>
>
> ~~
>
> Unfortunately, even though the publication parameters are the same
> again, the subscription seems to continue forever failing....
>
> ERROR:  cannot use different values of publish_generated_columns for
> table "public.t1" in different publications
>

The reason is that the failing 'insert' uses a historic snapshot,
which has a catalog state where 'publish_generated_columns' is still
false. So, you are seeing that error repeatedly. This behavior exists
from the very beginning of logical replication and another issue due
to the same reason was reported recently [1] which is actually a setup
issue. We should improve this situation some day but it is not the
responsibility of this patch.

[1] - https://www.postgresql.org/message-id/18683-a98f79c0673be358%40postgresql.org

-- 
With Regards,
Amit Kapila.