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

On Thu, 12 Sept 2024 at 11:01, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Because this feature is now being implemented as a PUBLICATION option,
> there is another scenario that might need consideration; I am thinking
> about where the same table is published by multiple PUBLICATIONS (with
> different option settings) that are subscribed by a single
> SUBSCRIPTION.
>
> e.g.1
> -----
> CREATE PUBLICATION pub1 FOR TABLE t1 WITH (publish_generated_columns = true);
> CREATE PUBLICATION pub2 FOR TABLE t1 WITH (publish_generated_columns = false);
> CREATE SUBSCRIPTION sub ... PUBLICATIONS pub1,pub2;
> -----
>
> e.g.2
> -----
> CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = true);
> CREATE PUBLICATION pub2 FOR TABLE t1 WITH (publish_generated_columns = false);
> CREATE SUBSCRIPTION sub ... PUBLICATIONS pub1,pub2;
> -----
>
> Do you know if this case is supported? If yes, then which publication
> option value wins?

I have verified the various scenarios discussed here and the patch
works as expected:
Test presetup:
-- publisher
CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, gen1 int GENERATED
ALWAYS AS (a * 2) STORED, gen2 int GENERATED ALWAYS AS (a * 2)
STORED);
-- Subscriber
CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, d int, e int);

Test1: Subscriber will have only non-generated columns a,b,c
replicated from publisher:
create publication pub1 for all tables with (
publish_generated_columns = false);
INSERT INTO t1 (a,b,c) VALUES (1,1,1);

--Subscriber will have only non-generated columns a,b,c replicated
from publisher:
subscriber=# select * from t1;
 a | b | c | d | e
---+---+---+---+---
 1 | 1 | 1 |   |
(1 row)

Test2: Subscriber will include generated columns a,b,c replicated from
publisher:
create publication pub1 for all tables with ( publish_generated_columns = true);
INSERT INTO t1 (a,b,c) VALUES (1,1,1);

-- Subscriber will include generated columns a,b,c replicated from publisher:
subscriber=# select * from t1;
 a | b | c | d | e
---+---+---+---+---
 1 | 1 | 1 | 2 | 2
(1 row)

Test3: Cannot have subscription subscribing to publication with
publish_generated_columns as true and false
-- publisher
create publication pub1 for all tables with (publish_generated_columns = false);
create publication pub2 for all tables with (publish_generated_columns = true);

-- subscriber
subscriber=# create subscription sub1 connection 'dbname=postgres
host=localhost port=5432' publication pub1,pub2;
ERROR:  cannot use different column lists for table "public.t1" in
different publications

Test4a: Warning thrown when a generated column is specified in column
list along with publish_generated_columns as false
-- publisher
postgres=# create publication pub1 for table t1(a,b,gen1) with (
publish_generated_columns = false);
WARNING:  specified generated column "gen1" in publication column list
for publication with publish_generated_columns as false
CREATE PUBLICATION

Regards,
Vignesh