Re: Pgoutput not capturing the generated columns

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