From: | vignesh C <vignesh21(at)gmail(dot)com> |
---|---|
To: | Peter Smith <smithpb2250(at)gmail(dot)com> |
Cc: | Shubham Khanna <khannashubham1197(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(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-09-23 11:57:04 |
Message-ID: | CALDaNm3Xmawn56W86WqJdoUry+GmWVAAurh+P3eSFezz17ZWLw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, 12 Sept 2024 at 11:01, Peter Smith <smithpb2250(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-09-23 11:58:56 | Re: Converting README documentation to Markdown |
Previous Message | David Rowley | 2024-09-23 11:53:42 | Re: ANALYZE ONLY |