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 ... ====== [1] https://www.postgresql.org/message-id/CAHut%2BPvtT8fKOfvVYr4vANx_fr92vedas%2BZRbQxvMC097rks6w%40mail.gmail.com Kind Regards, Peter Smith. Fujitsu Australia
