v22-0002-Support-replication-of-generated-column-during-i.patch

From 3cafc60b9073781d471d1b715b8350684b3d5d21 Mon Sep 17 00:00:00 2001
From: Shubham Khanna <shubham.khanna@fujitsu.com>
Date: Wed, 31 Jul 2024 11:47:08 +0530
Subject: [PATCH v22 2/4] Support replication of generated column during
 initial sync

When 'copy_data' is true, during the initial sync, the data is
replicated from
the publisher to the subscriber using the COPY command. The normal COPY
command does not copy generated columns, so when
'include_generated_columns'
is true, we need to copy using the syntax:
'COPY (SELECT column_name FROM table_name) TO STDOUT'.

Summary:

when (include_generated_columns = true)

* publisher not-generated column => subscriber not-generated column:
This is just normal logical replication (not changed by this patch).

* publisher not-generated column => subscriber generated column: This
will give ERROR.

* publisher generated column => subscriber not-generated column: The
publisher generated column value is copied.

* publisher generated column => subscriber generated column: The
publisher generated column value is not copied. The subscriber
generated column will be filled with the subscriber-side computed or
default data.

when (include_generated_columns = false)

* publisher not-generated column => subscriber not-generated column:
This is just normal logical replication (not changed by this patch).

* publisher not-generated column => subscriber generated column: This
will give ERROR.

* publisher generated column => subscriber not-generated column:
Publisher generated column is not replicated. The subscriber column
will be filled with the subscriber-side default data.

* publisher generated column => subscriber generated column: Publisher
generated column is not replicated. The subscriber generated column
will be filed with the subscriber-side computed or default data.
---
 doc/src/sgml/ref/create_subscription.sgml   |   4 -
 src/backend/commands/subscriptioncmds.c     |  14 --
 src/backend/replication/logical/relation.c  |   2 +-
 src/backend/replication/logical/tablesync.c | 139 +++++++++++++---
 src/include/replication/logicalrelation.h   |   3 +-
 src/test/regress/expected/subscription.out  |   2 -
 src/test/regress/sql/subscription.sql       |   1 -
 src/test/subscription/t/011_generated.pl    | 171 +++++++++++++-------
 8 files changed, 232 insertions(+), 104 deletions(-)

diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index ee27a5873a..8fb4491b65 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -442,10 +442,6 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
           has no effect; the subscriber column will be filled as normal with the
           subscriber-side computed or default data.
          </para>
-         <para>
-         This parameter can only be set <literal>true</literal> if <literal>copy_data</literal> is
-         set to <literal>false</literal>.
-         </para>
         </listitem>
        </varlistentry>
       </variablelist></para>
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 819a124c63..18b2a8e040 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -450,20 +450,6 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
 								"slot_name = NONE", "create_slot = false")));
 		}
 	}
-
-	/*
-	 * Do additional checking for disallowed combination when copy_data and
-	 * include_generated_columns are true. COPY of generated columns is not
-	 * supported yet.
-	 */
-	if (opts->copy_data && opts->include_generated_columns)
-	{
-		ereport(ERROR,
-				errcode(ERRCODE_SYNTAX_ERROR),
-		/*- translator: both %s are strings of the form "option = value" */
-				errmsg("%s and %s are mutually exclusive options",
-					   "copy_data = true", "include_generated_columns = true"));
-	}
 }
 
 /*
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 5de1531567..9de0b75330 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -205,7 +205,7 @@ logicalrep_relmap_update(LogicalRepRelation *remoterel)
  *
  * Returns -1 if not found.
  */
-static int
+int
 logicalrep_rel_att_by_name(LogicalRepRelation *remoterel, const char *attname)
 {
 	int			i;
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index e03e761392..2e90d42bdc 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -118,6 +118,7 @@
 #include "utils/builtins.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
+#include "utils/rel.h"
 #include "utils/rls.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
@@ -693,20 +694,67 @@ process_syncing_tables(XLogRecPtr current_lsn)
 
 /*
  * Create list of columns for COPY based on logical relation mapping.
+ * Exclude columns that are subscription table generated columns.
  */
 static List *
-make_copy_attnamelist(LogicalRepRelMapEntry *rel)
+make_copy_attnamelist(LogicalRepRelMapEntry *rel, bool *remotegenlist)
 {
 	List	   *attnamelist = NIL;
-	int			i;
+	bool	   *localgenlist;
+	TupleDesc	desc;
 
-	for (i = 0; i < rel->remoterel.natts; i++)
+	desc = RelationGetDescr(rel->localrel);
+	localgenlist = palloc0(rel->remoterel.natts * sizeof(bool));
+
+	/*
+	 * This loop checks for generated columns of the subscription table.
+	 */
+	for (int i = 0; i < desc->natts; i++)
 	{
-		attnamelist = lappend(attnamelist,
-							  makeString(rel->remoterel.attnames[i]));
+		int			remote_attnum;
+		Form_pg_attribute attr = TupleDescAttr(desc, i);
+
+		if (!attr->attgenerated)
+			continue;
+
+		remote_attnum = logicalrep_rel_att_by_name(&rel->remoterel,
+											NameStr(attr->attname));
+
+		if (remote_attnum >= 0)
+		{
+			/*
+			 * Check if the subscription table generated column has same
+			 * name as a non-generated column in the corresponding
+			 * publication table.
+			 */
+			if (!remotegenlist[remote_attnum])
+				ereport(ERROR,
+						(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+						 errmsg("logical replication target relation \"%s.%s\" has a generated column \"%s\" "
+								"but corresponding column on source relation is not a generated column",
+						 rel->remoterel.nspname, rel->remoterel.relname, NameStr(attr->attname))));
+
+			/*
+			 * 'localgenlist' records that this is a generated column in
+			 * the subscription table. Later, we use this information to
+			 * skip adding this column to the column list for COPY.
+			 */
+			localgenlist[remote_attnum] = true;
+		}
 	}
 
+	/*
+	 * Construct column list for COPY, excluding columns that are
+	 * subscription table generated columns.
+	 */
+	for (int i = 0; i < rel->remoterel.natts; i++)
+	{
+		if (!localgenlist[i])
+			attnamelist = lappend(attnamelist,
+								  makeString(rel->remoterel.attnames[i]));
+	}
 
+	pfree(localgenlist);
 	return attnamelist;
 }
 
@@ -791,19 +839,21 @@ copy_read_data(void *outbuf, int minread, int maxread)
  * qualifications to be used in the COPY command.
  */
 static void
-fetch_remote_table_info(char *nspname, char *relname,
+fetch_remote_table_info(char *nspname, char *relname, bool **remotegenlist_res,
 						LogicalRepRelation *lrel, List **qual)
 {
 	WalRcvExecResult *res;
 	StringInfoData cmd;
 	TupleTableSlot *slot;
 	Oid			tableRow[] = {OIDOID, CHAROID, CHAROID};
-	Oid			attrRow[] = {INT2OID, TEXTOID, OIDOID, BOOLOID};
+	Oid			attrRow[] = {INT2OID, TEXTOID, OIDOID, BOOLOID, BOOLOID};
 	Oid			qualRow[] = {TEXTOID};
 	bool		isnull;
+	bool	   *remotegenlist;
 	int			natt;
 	ListCell   *lc;
 	Bitmapset  *included_cols = NULL;
+	int			server_version = walrcv_server_version(LogRepWorkerWalRcvConn);
 
 	lrel->nspname = nspname;
 	lrel->relname = relname;
@@ -851,7 +901,7 @@ fetch_remote_table_info(char *nspname, char *relname,
 	 * We need to do this before fetching info about column names and types,
 	 * so that we can skip columns that should not be replicated.
 	 */
-	if (walrcv_server_version(LogRepWorkerWalRcvConn) >= 150000)
+	if (server_version >= 150000)
 	{
 		WalRcvExecResult *pubres;
 		TupleTableSlot *tslot;
@@ -948,18 +998,31 @@ fetch_remote_table_info(char *nspname, char *relname,
 					 "SELECT a.attnum,"
 					 "       a.attname,"
 					 "       a.atttypid,"
-					 "       a.attnum = ANY(i.indkey)"
+					 "       a.attnum = ANY(i.indkey)");
+
+	if(server_version >= 120000)
+		appendStringInfo(&cmd, ", a.attgenerated != ''");
+
+	appendStringInfo(&cmd,
 					 "  FROM pg_catalog.pg_attribute a"
 					 "  LEFT JOIN pg_catalog.pg_index i"
 					 "       ON (i.indexrelid = pg_get_replica_identity_index(%u))"
 					 " WHERE a.attnum > 0::pg_catalog.int2"
-					 "   AND NOT a.attisdropped %s"
+					 "   AND NOT a.attisdropped", lrel->remoteid);
+
+	if (server_version >= 120000)
+	{
+		bool gencols_allowed = server_version >= 180000 && MySubscription->includegencols;
+
+		if (!gencols_allowed)
+			appendStringInfo(&cmd, " AND a.attgenerated = ''");
+	}
+
+	appendStringInfo(&cmd,
 					 "   AND a.attrelid = %u"
 					 " ORDER BY a.attnum",
-					 lrel->remoteid,
-					 (walrcv_server_version(LogRepWorkerWalRcvConn) >= 120000 ?
-					  "AND a.attgenerated = ''" : ""),
 					 lrel->remoteid);
+
 	res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data,
 					  lengthof(attrRow), attrRow);
 
@@ -973,6 +1036,7 @@ fetch_remote_table_info(char *nspname, char *relname,
 	lrel->attnames = palloc0(MaxTupleAttributeNumber * sizeof(char *));
 	lrel->atttyps = palloc0(MaxTupleAttributeNumber * sizeof(Oid));
 	lrel->attkeys = NULL;
+	remotegenlist = palloc0(MaxTupleAttributeNumber * sizeof(bool));
 
 	/*
 	 * Store the columns as a list of names.  Ignore those that are not
@@ -1005,6 +1069,8 @@ fetch_remote_table_info(char *nspname, char *relname,
 		if (DatumGetBool(slot_getattr(slot, 4, &isnull)))
 			lrel->attkeys = bms_add_member(lrel->attkeys, natt);
 
+		remotegenlist[natt] = DatumGetBool(slot_getattr(slot, 5, &isnull));
+
 		/* Should never happen. */
 		if (++natt >= MaxTupleAttributeNumber)
 			elog(ERROR, "too many columns in remote table \"%s.%s\"",
@@ -1015,7 +1081,7 @@ fetch_remote_table_info(char *nspname, char *relname,
 	ExecDropSingleTupleTableSlot(slot);
 
 	lrel->natts = natt;
-
+	*remotegenlist_res = remotegenlist;
 	walrcv_clear_result(res);
 
 	/*
@@ -1037,7 +1103,7 @@ fetch_remote_table_info(char *nspname, char *relname,
 	 * 3) one of the subscribed publications is declared as TABLES IN SCHEMA
 	 * that includes this relation
 	 */
-	if (walrcv_server_version(LogRepWorkerWalRcvConn) >= 150000)
+	if (server_version >= 150000)
 	{
 		StringInfoData pub_names;
 
@@ -1123,10 +1189,13 @@ copy_table(Relation rel)
 	List	   *attnamelist;
 	ParseState *pstate;
 	List	   *options = NIL;
+	bool 	   *remotegenlist;
+	bool		gencol_copy_needed  = false;
 
 	/* Get the publisher relation info. */
 	fetch_remote_table_info(get_namespace_name(RelationGetNamespace(rel)),
-							RelationGetRelationName(rel), &lrel, &qual);
+							RelationGetRelationName(rel), &remotegenlist,
+							&lrel, &qual);
 
 	/* Put the relation into relmap. */
 	logicalrep_relmap_update(&lrel);
@@ -1135,11 +1204,31 @@ copy_table(Relation rel)
 	relmapentry = logicalrep_rel_open(lrel.remoteid, NoLock);
 	Assert(rel == relmapentry->localrel);
 
+	attnamelist = make_copy_attnamelist(relmapentry, remotegenlist);
+
 	/* Start copy on the publisher. */
 	initStringInfo(&cmd);
 
-	/* Regular table with no row filter */
-	if (lrel.relkind == RELKIND_RELATION && qual == NIL)
+	/*
+	 * Check if the remote table has any generated columns that should be copied.
+	 */
+	if (MySubscription->includegencols)
+	{
+		for (int i = 0; i < relmapentry->remoterel.natts; i++)
+		{
+			if (remotegenlist[i])
+			{
+				gencol_copy_needed = true;
+				break;
+			}
+		}
+	}
+
+	/*
+	 * Regular table with no row filter and copy of generated columns is
+	 * not necessary.
+	 */
+	if (lrel.relkind == RELKIND_RELATION && qual == NIL && !gencol_copy_needed)
 	{
 		appendStringInfo(&cmd, "COPY %s",
 						 quote_qualified_identifier(lrel.nspname, lrel.relname));
@@ -1173,13 +1262,20 @@ copy_table(Relation rel)
 		 * (SELECT ...), but we can't just do SELECT * because we need to not
 		 * copy generated columns. For tables with any row filters, build a
 		 * SELECT query with OR'ed row filters for COPY.
+		 *
+		 * We also need to use this same COPY (SELECT ...) syntax when
+		 * 'include_generated_columns' is specified as true and the remote
+		 * table has generated columns, because copy of generated columns is
+		 * not supported by the normal COPY.
 		 */
+		int i = 0;
+
 		appendStringInfoString(&cmd, "COPY (SELECT ");
-		for (int i = 0; i < lrel.natts; i++)
+		foreach_node(String, att_name, attnamelist)
 		{
-			appendStringInfoString(&cmd, quote_identifier(lrel.attnames[i]));
-			if (i < lrel.natts - 1)
+			if (i++)
 				appendStringInfoString(&cmd, ", ");
+			appendStringInfoString(&cmd, quote_identifier(strVal(att_name)));
 		}
 
 		appendStringInfoString(&cmd, " FROM ");
@@ -1237,7 +1333,6 @@ copy_table(Relation rel)
 	(void) addRangeTableEntryForRelation(pstate, rel, AccessShareLock,
 										 NULL, false, false);
 
-	attnamelist = make_copy_attnamelist(relmapentry);
 	cstate = BeginCopyFrom(pstate, rel, NULL, NULL, false, copy_read_data, attnamelist, options);
 
 	/* Do the copy */
diff --git a/src/include/replication/logicalrelation.h b/src/include/replication/logicalrelation.h
index e687b40a56..797e66dfdb 100644
--- a/src/include/replication/logicalrelation.h
+++ b/src/include/replication/logicalrelation.h
@@ -41,7 +41,8 @@ typedef struct LogicalRepRelMapEntry
 
 extern void logicalrep_relmap_update(LogicalRepRelation *remoterel);
 extern void logicalrep_partmap_reset_relmap(LogicalRepRelation *remoterel);
-
+extern int logicalrep_rel_att_by_name(LogicalRepRelation *remoterel,
+									  const char *attname);
 extern LogicalRepRelMapEntry *logicalrep_rel_open(LogicalRepRelId remoteid,
 												  LOCKMODE lockmode);
 extern LogicalRepRelMapEntry *logicalrep_partition_open(LogicalRepRelMapEntry *root,
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 3e08be39b7..e6eba1bea0 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -99,8 +99,6 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
 ERROR:  subscription with slot_name = NONE must also set create_slot = false
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
 ERROR:  subscription with slot_name = NONE must also set enabled = false
-CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (include_generated_columns = true, copy_data = true);
-ERROR:  copy_data = true and include_generated_columns = true are mutually exclusive options
 -- fail - include_generated_columns must be boolean
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, include_generated_columns = foo);
 ERROR:  include_generated_columns requires a Boolean value
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 7f7057d1b4..c88e7966bf 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -59,7 +59,6 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false);
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
-CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (include_generated_columns = true, copy_data = true);
 
 -- fail - include_generated_columns must be boolean
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, include_generated_columns = foo);
diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
index 05b83f6bec..e128567fe1 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -58,12 +58,13 @@ $node_publisher->safe_psql('postgres',
 $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)"
 );
 
-# publisher-side col 'b' is missing.
-# subscriber-side col 'b' is generated.
+# 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)");
@@ -125,10 +126,7 @@ $node_publisher->safe_psql('postgres',
 $node_publisher->safe_psql('postgres',
 	"CREATE PUBLICATION regress_pub1 FOR TABLE tab1");
 $node_publisher->safe_psql('postgres',
-	"CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_gen, tab_gen_to_nogen, tab_gen_to_missing, tab_missing_to_gen");
-$node_publisher->safe_psql('postgres',
-	"CREATE PUBLICATION regress_pub_nogen_to_gen FOR TABLE tab_nogen_to_gen");
-
+	"CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_gen, tab_gen_to_nogen, tab_missing_to_gen");
 $node_publisher->safe_psql('postgres',
 	"CREATE PUBLICATION regress_pub_misc FOR TABLE tab_order");
 
@@ -139,26 +137,17 @@ $node_subscriber->safe_psql('postgres',
 $node_subscriber->safe_psql('postgres',
 	"CREATE SUBSCRIPTION regress_sub_combo CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo"
 );
-
 $node_subscriber2->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub_combo2 CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo WITH (include_generated_columns = true, copy_data = false)"
+	"CREATE SUBSCRIPTION regress_sub_combo2 CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo WITH (include_generated_columns = true)"
 );
 $node_subscriber2->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_nogen_to_gen WITH (include_generated_columns = true, copy_data = false)"
-);
-
-$node_subscriber2->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub_misc CONNECTION '$publisher_connstr' PUBLICATION regress_pub_misc WITH (include_generated_columns = true, copy_data = false)"
+	"CREATE SUBSCRIPTION regress_sub_misc CONNECTION '$publisher_connstr' PUBLICATION regress_pub_misc WITH (include_generated_columns = true)"
 );
 
 #####################
 # Wait for initial sync of all subscriptions
 #####################
 
-# Here, copy_data = false because COPY and include_generated_columns are not
-# allowed at the same time for patch 0001.
-# And that is why all expected results on subscriber2 will be empty.
-# This limitation will be changed in patch 0002.
 
 $node_subscriber->wait_for_subscription_sync;
 $node_subscriber2->wait_for_subscription_sync;
@@ -174,7 +163,9 @@ is( $result, qq(1|21
 2|22
 3|23), 'generated columns initial sync, when include_generated_columns=false');
 $result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen");
-is( $result, qq(), 'generated columns initial sync, when include_generated_columns=true');
+is( $result, qq(1|21
+2|22
+3|23), 'generated columns initial sync, when include_generated_columns=true');
 
 # gen-to-nogen
 $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_nogen");
@@ -182,11 +173,9 @@ is( $result, qq(1|
 2|
 3|), 'generated columns initial sync, when include_generated_columns=false');
 $result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_nogen");
-is( $result, qq(), 'generated columns initial sync, when include_generated_columns=true');
-
-# nogen-to-gen
-$result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_nogen_to_gen");
-is( $result, qq(), 'generated columns initial sync, when include_generated_columns=true');
+is( $result, qq(1|2
+2|4
+3|6), 'generated columns initial sync, when include_generated_columns=true');
 
 # missing-to_gen
 $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_missing_to_gen");
@@ -194,11 +183,15 @@ is( $result, qq(1|2
 2|4
 3|6), 'generated columns initial sync, when include_generated_columns=false');
 $result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_missing_to_gen");
-is( $result, qq(), 'generated columns initial sync, when include_generated_columns=true');
+is( $result, qq(1|2
+2|4
+3|6), 'generated columns initial sync, when include_generated_columns=true');
 
 $result = $node_subscriber2->safe_psql('postgres',
 	"SELECT a, b, c FROM tab_order ORDER BY a");
-is( $result, qq(), 'generated column initial sync');
+is( $result, qq(1|2|22
+2|4|44
+3|6|66), 'generated column initial sync');
 
 $result = $node_subscriber2->safe_psql('postgres',
 	"SELECT a, b, c FROM tab_alter ORDER BY a");
@@ -251,7 +244,10 @@ is( $result, qq(1|21
 $node_publisher->wait_for_catchup('regress_sub_combo2');
 $result =
   $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen ORDER BY a");
-is( $result, qq(4|24
+is( $result, qq(1|21
+2|22
+3|23
+4|24
 5|25),
 	'confirm generated columns are NOT replicated when the subscriber-side column is also generated'
 );
@@ -284,35 +280,14 @@ is( $result, qq(1|
 $node_publisher->wait_for_catchup('regress_sub_combo2');
 $result =
   $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_nogen ORDER BY a");
-is( $result, qq(4|8
+is( $result, qq(1|2
+2|4
+3|6
+4|8
 5|10),
 	'confirm generated columns are replicated when the subscriber-side column is not generated'
 );
 
-#####################
-# TEST tab_nogen_to_gen
-#
-# publisher-side has generated col 'b'.
-# subscriber-side has non-generated col 'b'.
-#####################
-
-# insert data
-$node_publisher->safe_psql('postgres', "INSERT INTO tab_nogen_to_gen VALUES (4), (5)");
-
-# regress_sub_nogen_to_gen: (include_generated_columns = false)
-# Confirm that col 'b' is not replicated.
-$node_publisher->wait_for_catchup('regress_sub_nogen_to_gen');
-$result =
-  $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_nogen_to_gen ORDER BY a");
-is( $result, qq(4|88
-5|110),
-	'confirm generated columns are replicated when the subscriber-side column is not generated'
-);
-
-#Cleanup
-$node_publisher->safe_psql('postgres',"DROP PUBLICATION regress_pub_nogen_to_gen");
-$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub_nogen_to_gen");
-
 #####################
 # TEST tab_missing_to_gen
 #
@@ -341,7 +316,10 @@ is( $result, qq(1|2
 $node_publisher->wait_for_catchup('regress_sub_combo2');
 $result =
   $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_missing_to_gen ORDER BY a");
-is( $result, qq(4|8
+is( $result, qq(1|2
+2|4
+3|6
+4|8
 5|10),
 	'confirm when publisher col is missing, subscriber generated columns are generated as normal'
 );
@@ -351,6 +329,78 @@ $node_publisher->safe_psql('postgres',"DROP PUBLICATION regress_pub_combo");
 $node_subscriber->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub_combo");
 $node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub_combo2");
 
+#####################
+# TEST tab_gen_to_missing
+#
+# publisher-side col 'b' is generated.
+# subscriber-side col 'b' is missing
+#####################
+
+# regress_sub1_gen_to_missing: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_gen_to_missing FOR TABLE tab_gen_to_missing");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_gen_to_missing"
+);
+$node_publisher->wait_for_catchup('regress_sub1_gen_to_missing');
+$result = $node_subscriber->safe_psql('postgres', "SELECT a FROM tab_gen_to_missing");
+is( $result, qq(1
+2
+3), 'missing generated column, include_generated_columns = false');
+
+# regress_sub2_gen_to_missing: (include_generated_columns = true)
+# Confirm that col 'b' s not replicated and it will throw an error.
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+my $offset2 = -s $node_subscriber2->logfile;
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_gen_to_missing with (include_generated_columns = true)"
+);
+$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_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_gen_to_missing");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION regress_sub1_gen_to_missing");
+$node_subscriber2->safe_psql('postgres', "DROP SUBSCRIPTION regress_sub2_gen_to_missing");
+
+#####################
+# TEST tab_nogen_to_gen
+#
+# publisher-side col 'b' is not-generated.
+# subscriber-side col 'b' is generated
+#####################
+
+# regress_sub1_nogen_to_gen: (include_generated_columns = false)
+# Confirm that col 'b' s not replicated and it will throw an error.
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+my $offset = -s $node_subscriber->logfile;
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION regress_pub_nogen_to_gen FOR TABLE tab_nogen_to_gen");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub1_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_nogen_to_gen WITH (include_generated_columns = false)"
+);
+$node_subscriber->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? 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/,
+	$offset);
+
+# regress_sub2_nogen_to_gen: (include_generated_columns = true)
+# Confirm that col 'b' s not replicated and it will throw an error.
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+$node_subscriber2->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_nogen_to_gen WITH (include_generated_columns = true)"
+);
+$node_subscriber2->wait_for_log(
+	qr/ERROR: ( [A-Z0-9]:)? 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/,
+	$offset2);
+
+#Cleanup
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_nogen_to_gen");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION regress_sub1_nogen_to_gen");
+$node_subscriber2->safe_psql('postgres', "DROP SUBSCRIPTION regress_sub2_nogen_to_gen");
+
 #####################
 # TEST tab_order:
 #
@@ -368,7 +418,10 @@ $node_publisher->wait_for_catchup('regress_sub_misc');
 $result =
   $node_subscriber2->safe_psql('postgres',
 	"SELECT a, b, c FROM tab_order ORDER BY a");
-is( $result, qq(4|8|88
+is( $result, qq(1|2|22
+2|4|44
+3|6|66
+4|8|88
 5|10|110), 'replicate generated columns with different order on subscriber');
 
 #####################
@@ -385,9 +438,9 @@ $node_subscriber2->safe_psql('postgres',
 $node_publisher->wait_for_catchup('regress_sub_misc');
 $result = $node_subscriber2->safe_psql('postgres',
 	"SELECT a, b, c FROM tab_alter ORDER BY a");
-is( $result, qq(1||22
-2||44
-3||66), 'add new table to existing publication');
+is( $result, qq(1|2|22
+2|4|44
+3|6|66), 'add new table to existing publication');
 
 #####################
 # TEST tabl_alter
@@ -407,9 +460,9 @@ $node_publisher->safe_psql('postgres',
 # confirmed 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(1||22
-2||44
-3||66
+is( $result, qq(1|2|22
+2|4|44
+3|6|66
 4|8|8
 5|10|10), 'after drop generated column expression');
 
-- 
2.34.1