Re: Pgoutput not capturing the generated columns

On Mon, Aug 5, 2024 at 9:15 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> Hi,
>
> Writing many new test case combinations has exposed a possible bug in
> patch 0001.
>
> In my previous post [1] there was questionable behaviour when
> replicating from a normal (not generated) column on the publisher side
> to a generated column on the subscriber side. Initially, I thought the
> test might have exposed a possible PG17 bug, but now I think it has
> really found a bug in patch 0001.
>
> ~~~
>
> Previously (PG17) this would fail consistently both during COPY and
> during normal replication.Now, patch 0001 has changed this behaviour
> -- it is not always failing anymore.
>
> The patch should not be impacting this existing behaviour. It only
> introduces a new 'include_generated_columns', but since the publisher
> side is not a generated column I do not expect there should be any
> difference in behaviour for this test case. IMO the TAP test expected
> results should be corrected for this scenario. And fix the bug.
>
> Below is an example demonstrating PG17 behaviour.
>
> ======
>
>
> Publisher:
> ----------
>
> (notice column "b" is not generated)
>
> test_pub=# CREATE TABLE tab_nogen_to_gen (a int, b int);
> CREATE TABLE
> test_pub=# INSERT INTO tab_nogen_to_gen VALUES (1,101),(2,102);
> INSERT 0 2
> test_pub=# CREATE PUBLICATION pub1 for TABLE tab_nogen_to_gen;
> CREATE PUBLICATION
> test_pub=#
>
> Subscriber:
> -----------
>
> (notice corresponding column "b" is generated)
>
> test_sub=# CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED
> ALWAYS AS (a * 22) STORED);
> CREATE TABLE
> test_sub=#
>
> Try to create a subscription. Notice we get the error: ERROR: logical
> replication target relation "public.tab_nogen_to_gen" is missing
> replicated column: "b"
>
> test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub'
> PUBLICATION pub1;
> 2024-08-05 13:16:40.043 AEST [20957] WARNING: subscriptions created
> by regression test cases should have names starting with "regress_"
> WARNING: subscriptions created by regression test cases should have
> names starting with "regress_"
> NOTICE: created replication slot "sub1" on publisher
> CREATE SUBSCRIPTION
> test_sub=# 2024-08-05 13:16:40.105 AEST [29258] LOG: logical
> replication apply worker for subscription "sub1" has started
> 2024-08-05 13:16:40.117 AEST [29260] LOG: logical replication table
> synchronization worker for subscription "sub1", table
> "tab_nogen_to_gen" has started
> 2024-08-05 13:16:40.172 AEST [29260] ERROR: logical replication
> target relation "public.tab_nogen_to_gen" is missing replicated
> column: "b"
> 2024-08-05 13:16:40.173 AEST [20039] LOG: background worker "logical
> replication tablesync worker" (PID 29260) exited with exit code 1
> 2024-08-05 13:16:45.187 AEST [29400] LOG: logical replication table
> synchronization worker for subscription "sub1", table
> "tab_nogen_to_gen" has started
> 2024-08-05 13:16:45.285 AEST [29400] ERROR: logical replication
> target relation "public.tab_nogen_to_gen" is missing replicated
> column: "b"
> 2024-08-05 13:16:45.286 AEST [20039] LOG: background worker "logical
> replication tablesync worker" (PID 29400) exited with exit code 1
> ...
>
> Create the subscription again, but this time with copy_data = false
>
> test_sub=# CREATE SUBSCRIPTION sub1_nocopy CONNECTION
> 'dbname=test_pub' PUBLICATION pub1 WITH (copy_data = false);
> 2024-08-05 13:22:57.719 AEST [20957] WARNING: subscriptions created
> by regression test cases should have names starting with "regress_"
> WARNING: subscriptions created by regression test cases should have
> names starting with "regress_"
> NOTICE: created replication slot "sub1_nocopy" on publisher
> CREATE SUBSCRIPTION
> test_sub=# 2024-08-05 13:22:57.765 AEST [7012] LOG: logical
> replication apply worker for subscription "sub1_nocopy" has started
>
> test_sub=#
>
> ~~~
>
> Then insert data from the publisher to see what happens for normal replication.
>
> test_pub=#
> test_pub=# INSERT INTO tab_nogen_to_gen VALUES (3,103),(4,104);
> INSERT 0 2
>
> ~~~
>
> Notice the subscriber gets the same error as before: ERROR: logical
> replication target relation "public.tab_nogen_to_gen" is missing
> replicated column: "b"
>
> 2024-08-05 13:25:14.897 AEST [20039] LOG: background worker "logical
> replication apply worker" (PID 10957) exited with exit code 1
> 2024-08-05 13:25:19.933 AEST [11095] LOG: logical replication apply
> worker for subscription "sub1_nocopy" has started
> 2024-08-05 13:25:19.966 AEST [11095] ERROR: logical replication
> target relation "public.tab_nogen_to_gen" is missing replicated
> column: "b"
> 2024-08-05 13:25:19.966 AEST [11095] CONTEXT: processing remote data
> for replication origin "pg_16390" during message type "INSERT" in
> transaction 742, finished at 0/1967BB0
> 2024-08-05 13:25:19.968 AEST [20039] LOG: background worker "logical
> replication apply worker" (PID 11095) exited with exit code 1
> 2024-08-05 13:25:24.917 AEST [11225] LOG: logical replication apply
> worker for subscription "sub1_nocopy" has started
> 2024-08-05 13:25:24.926 AEST [11225] ERROR: logical replication
> target relation "public.tab_nogen_to_gen" is missing replicated
> column: "b"
> 2024-08-05 13:25:24.926 AEST [11225] CONTEXT: processing remote data
> for replication origin "pg_16390" during message type "INSERT" in
> transaction 742, finished at 0/1967BB0
> 2024-08-05 13:25:24.927 AEST [20039] LOG: background worker "logical
> replication apply worker" (PID 11225) exited with exit code 1
>
This is an expected behaviour. The error message here is improvised.
This error is consistent and it is being handled in the 0002 patch.
Below are the logs for the same:
2024-08-07 10:47:45.977 IST [29756] LOG: logical replication table
synchronization worker for subscription "sub1", table
"tab_nogen_to_gen" has started
2024-08-07 10:47:46.116 IST [29756] ERROR: logical replication target
relation "public.tab_nogen_to_gen" has a generated column "b" but
corresponding column on source relation is not a generated column
0002 Patch needs to be applied to get rid of this error.

Thanks and Regards,
Shubham Khanna.