Re: Pgoutput not capturing the generated columns - Mailing list pgsql-hackers

Hi Vignesh,

I am observing some unexpected errors with the following scenario.

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


~~

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

~~

I didn't think a REFRESH PUBLICATION was necessary for this case, but
anyway that does not seem to make any difference.

test_sub=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION

... still getting repeating error
2024-11-06 16:54:44.839 AEDT [5659] ERROR:  could not receive data
from WAL stream: ERROR:  cannot use different values of
publish_generated_columns for table "public.t1" in different
publications

======

To summarize -- Altering the publication parameter combination from
good to bad has an immediate effect on breaking the subscription, but
then altering it back again from bad to good seemed to do nothing  at
all (the subscription just remains broken).

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