Re: Pgoutput not capturing the generated columns

From: Ajin Cherian <itsajin(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Peter Smith <smithpb2250(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Shubham Khanna <khannashubham1197(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Rajendra Kumar Dangwal <dangwalrajendra888(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, euler(at)eulerto(dot)com
Subject: Re: Pgoutput not capturing the generated columns
Date: 2024-11-04 04:30:29
Message-ID: CAFPTHDbD3BjDaYabvxzDdwGEEeVNmp=2F5YrjBk_wkzjNu+dNw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Nov 4, 2024 at 2:20 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Oct 31, 2024 at 4:26 PM Ajin Cherian <itsajin(at)gmail(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amul Sul 2024-11-04 04:40:01 Re: small pg_combinebackup improvements
Previous Message Alexander Korotkov 2024-11-04 04:29:42 Re: pgsql: Implement pg_wal_replay_wait() stored procedure