Re: Pgoutput not capturing the generated columns

On Mon, Nov 4, 2024 at 2:20 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Thu, Oct 31, 2024 at 4:26 PM Ajin Cherian <itsajin@gmail.com> wrote:
> >
> > 5. Verified that publications with different column list are disallowed to be subscribed by one subscription
> >    a. PUB_A(column list = (a, b)) PUB_B(no column list, with publish_generated_column) - OK
> >    b. PUB_A(column list = (a, b)) PUB_B(no column list, without publish_generated_column) - FAIL
> >    c.  PUB_A(no column list, without publish_generated_column) PUB_B(no column list, with publish_generated_column) - FAIL
> >
> > Tests did not show any unexpected behaviour.
> >
>
> Thanks for the tests, but the results of step 5 do not clearly show
> whether they are correct because you haven't shared the table schema.
>

Here are the tests:
5. Verified that publications with different column list are
disallowed to be subscribed by one subscription
   a. PUB_A(column list = (a, b)) PUB_B(no column list, with
publish_generated_column) - OK
PUB:
CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
CREATE PUBLICATION pub1 FOR table gencols with (publish_generated_columns=true);
CREATE PUBLICATION pub2 FOR table gencols(a,gen1);

SUB:
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=postgres
host=localhost port=6972' PUBLICATION pub1, pub2;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION


   b. PUB_A(column list = (a, b)) PUB_B(no column list, without
publish_generated_column) - FAIL
PUB:
CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
CREATE PUBLICATION pub1 FOR table gencols with
(publish_generated_columns=false);
CREATE PUBLICATION pub2 FOR table gencols(a,gen1);

SUB:
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=postgres
host=localhost port=6972' PUBLICATION pub1, pub2;
ERROR:  cannot use different column lists for table "public.gencols"
in different publications

   c.  PUB_A(no column list, without publish_generated_column)
PUB_B(no column list, with publish_generated_column) - FAIL
PUB:
CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
CREATE PUBLICATION pub1 FOR table gencols with
(publish_generated_columns=false);
CREATE PUBLICATION pub2 FOR table gencols with (publish_generated_columns=true);

SUB:
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=postgres
host=localhost port=6972' PUBLICATION pub1, pub2;
ERROR:  cannot use different column lists for table "public.gencols"
in different publications

regards,
Ajin Cherian
Fujitsu Australia