v27-0002-Tap-tests-for-include-generated-columns.patch

From ae61cda76dbc5d3666551a23607df2ea32821e54 Mon Sep 17 00:00:00 2001
From: Khanna <Shubham.Khanna@fujitsu.com>
Date: Fri, 16 Aug 2024 16:04:30 +0530
Subject: [PATCH v27 2/2] Tap tests for 'include-generated-columns'

Tap tests for 'include-generated-columns'
---
 src/test/subscription/t/011_generated.pl | 521 ++++++++++++++++++++++-
 1 file changed, 517 insertions(+), 4 deletions(-)

diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
index 8b2e5f4708..5f4b1e4cce 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -12,12 +12,30 @@ use Test::More;
 
 my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
 $node_publisher->init(allows_streaming => 'logical');
+$node_publisher->append_conf(
+	'postgresql.conf',
+	"max_wal_senders = 20
+	 max_replication_slots = 20");
 $node_publisher->start;
 
+# All subscribers on this node will use parameter include_generated_columns = false
 my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
 $node_subscriber->init;
+$node_subscriber->append_conf(
+	'postgresql.conf',
+	"max_logical_replication_workers = 20
+	 max_worker_processes = 20");
 $node_subscriber->start;
 
+# All subscribers on this node will use parameter include_generated_columns = true
+my $node_subscriber2 = PostgreSQL::Test::Cluster->new('subscriber2');
+$node_subscriber2->init;
+$node_subscriber2->append_conf(
+	'postgresql.conf',
+	"max_logical_replication_workers = 20
+	 max_worker_processes = 20");
+$node_subscriber2->start;
+
 my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
 
 $node_publisher->safe_psql('postgres',
@@ -28,32 +46,272 @@ $node_subscriber->safe_psql('postgres',
 	"CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) STORED, c int)"
 );
 
+# tab_gen_to_gen:
+# publisher-side has generated col 'b'.
+# subscriber-side has generated col 'b', with different computation.
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_gen_to_nogen:
+# publisher-side has generated col 'b'.
+# subscriber-side has non-generated col 'b'.
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_nogen (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_nogen (a int, b int)");
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_nogen (a int, b int)");
+
+# tab_gen_to_missing:
+# publisher-side has generated col 'b'.
+# subscriber-side col 'b' is missing.
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_missing (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_missing (a int)");
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_gen_to_missing (a int)");
+
+# tab_missing_to_gen:
+# publisher-side col 'b' is missing.
+# subscriber-side has generated col 'b'.
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_missing_to_gen (a int)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_nogen_to_gen:
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_nogen_to_gen (a int, b int)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_order:
+# publisher-side has generated cols 'b' and 'c'.
+# subscriber-side has non-generated col 'b', and generated-col 'c'.
+# columns on publisher/subscriber are in a different order
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_order (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_order (c int GENERATED ALWAYS AS (a * 22) STORED, a int, b int)"
+);
+
+# tab_alter:
+# for testing ALTER SUBSCRIPTION ... REFRESH PUBLICATION
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_alter (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE TABLE tab_alter (a int, b int, c int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
 # data for initial sync
 
 $node_publisher->safe_psql('postgres',
 	"INSERT INTO tab1 (a) VALUES (1), (2), (3)");
 
 $node_publisher->safe_psql('postgres',
-	"CREATE PUBLICATION pub1 FOR ALL TABLES");
+	"INSERT INTO tab_gen_to_gen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_gen_to_nogen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_gen_to_missing (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_missing_to_gen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_nogen_to_gen (a, b) VALUES (1, 1), (2, 2), (3, 3)");
+
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_order (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_alter (a) VALUES (1), (2), (3)");
+
+# create publications
+#
+# pub_combo_gen_to_missing is not included in pub_combo, because some tests give errors.
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_tab1 FOR TABLE tab1");
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_nogen, tab_missing_to_gen"
+);
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_combo_gen_to_missing FOR TABLE tab_gen_to_missing"
+);
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_combo_nogen_to_gen FOR TABLE tab_nogen_to_gen"
+);
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_combo_gen_to_gen FOR TABLE tab_gen_to_gen"
+);
+
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_misc FOR TABLE tab_order");
+
+# create subscriptions
+#
+# Note that all subscriptions created on node_subscriber2 use copy_data = false,
+# because copy_data = true with include_generated_columns is not yet supported.
+# For this reason, the expected inital data on node_subscriber2 is always empty.
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_tab1 CONNECTION '$publisher_connstr' PUBLICATION regress_pub_tab1"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_combo CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo"
+);
 $node_subscriber->safe_psql('postgres',
-	"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+	"CREATE SUBSCRIPTION regress_sub1_combo_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing"
 );
+# Note, regress_sub1_combo_nogen_to_gen is not created here due to expected errors. See later.
 
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo WITH (include_generated_columns = true, copy_data = false)"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing with (include_generated_columns = true, copy_data = false)"
+);
+
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_misc CONNECTION '$publisher_connstr' PUBLICATION regress_pub_misc WITH (include_generated_columns = true, copy_data = false)"
+);
+
+#####################
 # Wait for initial sync of all subscriptions
+#####################
+
 $node_subscriber->wait_for_subscription_sync;
+$node_subscriber2->wait_for_subscription_sync;
 
 my $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab1");
 is( $result, qq(1|22
 2|44
 3|66), 'generated columns initial sync');
 
+#####################
+# TEST tab_gen_to_gen initial sync
+#####################
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_combo_gen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_gen"
+);
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo_gen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_gen WITH (include_generated_columns = true, copy_data = false)"
+);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo_gen_to_gen");
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo_gen_to_gen");
+
+#####################
+# TEST tab_gen_to_nogen initial sync
+#####################
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT a, b FROM tab_gen_to_nogen");
+is( $result, qq(1|
+2|
+3|), 'tab_gen_to_nogen, when include_generated_columns=false');
+$result = $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b FROM tab_gen_to_nogen");
+is($result, qq(),
+	'tab_gen_to_nogen initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_gen_to_missing initial sync
+#####################
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT a FROM tab_gen_to_missing");
+is( $result, qq(1
+2
+3), 'tab_gen_to_missing initial sync, when include_generated_columns=false');
+# Note, the following is expected to work only because copy_data = false
+$result =
+  $node_subscriber2->safe_psql('postgres',
+	"SELECT a FROM tab_gen_to_missing");
+is($result, qq(),
+	'tab_gen_to_missing initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_missing_to_gen initial sync
+#####################
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT a, b FROM tab_missing_to_gen");
+is( $result, qq(1|22
+2|44
+3|66), 'tab_missing_to_gen initial sync, when include_generated_columns=false'
+);
+$result = $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b FROM tab_missing_to_gen");
+is($result, qq(),
+	'tab_missing_to_gen initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_nogen_to_gen initial sync
+#####################
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+my $offset = -s $node_subscriber->logfile;
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen"
+);
+$node_subscriber->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/,
+	$offset);
+my $offset2 = -s $node_subscriber2->logfile;
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH (include_generated_columns = true, copy_data = false)"
+);
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_nogen_to_gen VALUES (4), (5)");
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/,
+	$offset2);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo_nogen_to_gen");
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo_nogen_to_gen");
+
+# tab_order:
+$result = $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b, c FROM tab_order ORDER BY a");
+is($result, qq(), 'generated column initial sync');
+
+# tab_alter:
+$result = $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b, c FROM tab_alter ORDER BY a");
+is($result, qq(), 'unsubscribed table initial data');
+
 # data to replicate
 
 $node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (4), (5)");
 
 $node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 6 WHERE a = 5");
 
-$node_publisher->wait_for_catchup('sub1');
+$node_publisher->wait_for_catchup('regress_sub1_tab1');
 
 $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1");
 is( $result, qq(1|22|
@@ -62,6 +320,261 @@ is( $result, qq(1|22|
 4|88|
 6|132|), 'generated columns replicated');
 
+#####################
+# TEST tab_gen_to_gen replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side has generated col 'b', using a different computation.
+#####################
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_combo_gen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_gen"
+);
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_gen_to_gen VALUES (4), (5)");
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo_gen_to_gen");
+
+# regress_sub2_combo_gen_to_gen: (include_generated_columns = true)
+# When copy_data=false, no COPY error occurs.
+# The col 'b' is not replicated; the subscriber-side generated value is inserted.
+#
+# XXX
+# It is correct for this to give the same result as above, but it needs more
+# study to determine if the above result was actually correct, or a PG17 bug.
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo_gen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_gen WITH (include_generated_columns = true, copy_data = false)"
+);
+
+# cleanup
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo_gen_to_gen");
+$node_publisher->safe_psql('postgres',
+	"DROP PUBLICATION regress_pub_combo_gen_to_gen");
+
+#####################
+# TEST tab_gen_to_nogen replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side has non-generated col 'b'.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_gen_to_nogen VALUES (4), (5)");
+
+# regress_sub1_combo: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+$node_publisher->wait_for_catchup('regress_sub1_combo');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT a, b FROM tab_gen_to_nogen ORDER BY a");
+is( $result, qq(1|
+2|
+3|
+4|
+5|),
+	'confirm generated columns are not replicated when the subscriber-side column is not generated'
+);
+
+# regress_sub2_combo: (include_generated_columns = true)
+# Confirm that col 'b' is replicated.
+$node_publisher->wait_for_catchup('regress_sub2_combo');
+$result =
+  $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b FROM tab_gen_to_nogen ORDER BY a");
+is( $result, qq(4|8
+5|10),
+	'confirm generated columns are replicated when the subscriber-side column is not generated'
+);
+
+#####################
+# TEST tab_gen_to_missing replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side col 'b' is missing.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_gen_to_missing VALUES (4), (5)");
+
+# regress_sub1_combo_gen_to_missing: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+$node_publisher->wait_for_catchup('regress_sub1_combo_gen_to_missing');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT a FROM tab_gen_to_missing ORDER BY a");
+is( $result, qq(1
+2
+3
+4
+5),
+	'missing generated column, include_generated_columns = false');
+
+# regress_sub2_combo_gen_to_missing: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated and it will throw an error.
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]+:)? logical replication target relation "public.tab_gen_to_missing" is missing replicated column: "b"/,
+	$offset2);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo_gen_to_missing");
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo_gen_to_missing");
+$node_publisher->safe_psql('postgres',
+	"DROP PUBLICATION regress_pub_combo_gen_to_missing");
+
+#####################
+# TEST tab_missing_to_gen replication
+#
+# publisher-side col 'b' is missing.
+# subscriber-side col 'b' is generated.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_missing_to_gen VALUES (4), (5)");
+
+# regress_sub1_combo: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated, but is generated as normal
+$node_publisher->wait_for_catchup('regress_sub1_combo');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT a, b FROM tab_missing_to_gen ORDER BY a");
+is( $result, qq(1|22
+2|44
+3|66
+4|88
+5|110),
+	'confirm when publisher col is missing, subscriber generated columns are generated as normal'
+);
+
+# regress_sub2_combo: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated, but is generated as normal
+$node_publisher->wait_for_catchup('regress_sub2_combo');
+$result =
+  $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b FROM tab_missing_to_gen ORDER BY a");
+is( $result, qq(4|88
+5|110),
+	'confirm when publisher col is missing, subscriber generated columns are generated as normal'
+);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo");
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_combo");
+
+#####################
+# TEST tab_nogen_to_gen replication
+#
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
+#####################
+
+# When copy_data=true a COPY error occurred. Try again but with copy_data=false.
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen"
+);
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_nogen_to_gen VALUES (4), (5)");
+
+$node_subscriber->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/,
+	$offset);
+
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub1_combo_nogen_to_gen");
+
+# regress_sub2_combo_nogen_to_gen: (include_generated_columns = true)
+# When copy_data=false, no COPY error occurs.
+# The col 'b' is not replicated; the subscriber-side generated value is inserted.
+#
+# XXX
+# It is correct for this to give the same result as above, but it needs more
+# study to determine if the above result was actually correct, or a PG17 bug.
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH (include_generated_columns = true, copy_data = false)"
+);
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/,
+	$offset2);
+
+# cleanup
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_combo_nogen_to_gen");
+$node_publisher->safe_psql('postgres',
+	"DROP PUBLICATION regress_pub_combo_nogen_to_gen");
+
+#####################
+# TEST tab_order replication
+#
+# publisher-side cols 'b' and 'c' are generated
+# subscriber-side col 'b' is not generated and col 'c' is generated.
+# But pub/sub table cols are in different order.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_order VALUES (4), (5)");
+
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_order" is missing replicated column: "c"/,
+	$offset2);
+
+#####################
+# TEST tab_alter replication
+#
+# Add a new table to existing publication, then
+# do ALTER SUBSCRIPTION ... REFRESH PUBLICATION
+#####################
+
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION regress_pub_misc ADD TABLE tab_alter");
+$node_subscriber2->safe_psql('postgres',
+	"ALTER SUBSCRIPTION regress_sub2_misc REFRESH PUBLICATION");
+
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_order" is missing replicated column: "c"/,
+	$offset2);
+
+#####################
+# TEST tab_alter
+#
+# Drop the generated column's expression on subscriber side.
+# This changes the generated column into a non-generated column.
+#####################
+
+# change a gencol to a nogen col
+$node_subscriber2->safe_psql('postgres',
+	"ALTER TABLE tab_alter ALTER COLUMN c DROP EXPRESSION");
+
+# insert some data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_alter (a) VALUES (4), (5)");
+
+# confirm that replication now works for the subscriber nogen col
+$result = $node_subscriber2->safe_psql('postgres',
+	"SELECT a, b, c FROM tab_alter ORDER BY a");
+is($result, qq(), 'after drop generated column expression');
+
+# cleanup
+$node_subscriber2->safe_psql('postgres',
+	"DROP SUBSCRIPTION regress_sub2_misc");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_misc");
+
+#####################
 # try it with a subscriber-side trigger
 
 $node_subscriber->safe_psql(
@@ -84,7 +597,7 @@ $node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (7), (8)");
 
 $node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 9 WHERE a = 7");
 
-$node_publisher->wait_for_catchup('sub1');
+$node_publisher->wait_for_catchup('regress_sub1_tab1');
 
 $result =
   $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY 1");
-- 
2.41.0.windows.3