[Patch] Use *other* indexes on the subscriber when REPLICA IDENTITY is FULL

From 9b102ac120313b4443e15a7d3d46a59a8b263ff3 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Date: Mon, 19 Jun 2023 08:28:00 +0000
Subject: [PATCH v2] Allow to use Hash index on subscriber

---
 src/backend/executor/execReplication.c     | 69 ++++++++++++++--
 src/backend/replication/logical/relation.c | 27 +++++--
 src/backend/utils/cache/lsyscache.c        | 22 +++++
 src/include/executor/executor.h            |  3 +
 src/include/utils/lsyscache.h              |  1 +
 src/test/subscription/meson.build          |  1 +
 src/test/subscription/t/034_hash.pl        | 93 ++++++++++++++++++++++
 7 files changed, 204 insertions(+), 12 deletions(-)
 create mode 100644 src/test/subscription/t/034_hash.pl

diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 9dd7168461..023585b619 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -19,6 +19,8 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/pg_am_d.h"
+#include "commands/defrem.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
@@ -41,15 +43,67 @@
 static bool tuples_equal(TupleTableSlot *slot1, TupleTableSlot *slot2,
 						 TypeCacheEntry **eq);
 
+/*
+ * Return the strategy number which corresponds to the equality operator for
+ * given index access method.
+ *
+ * TODO: support other indexes: GiST, SP-GiST, other user-defined idexes.
+ */
+int
+get_equal_strategy_number_for_am(Oid am)
+{
+	int ret;
+
+	switch (am)
+	{
+		case BTREE_AM_OID:
+			ret = BTEqualStrategyNumber;
+			break;
+		case HASH_AM_OID:
+			ret = HTEqualStrategyNumber;
+			break;
+		case GIST_AM_OID:
+		case SPGIST_AM_OID:
+			/* TODO */
+		case GIN_AM_OID:
+		case BRIN_AM_OID:
+			/*
+			 * XXX: GIN and BRIN do not support for the moment because they do not
+			 * implement amgettuple API.
+			 */
+		default:
+			/* XXX: Do we have to support extended indexes? */
+			ret = InvalidStrategy;
+			break;
+	}
+
+	return ret;
+}
+
+/*
+ * Return the appropriate strategy number which corresponds to the equality
+ * operator.
+ *
+ * TODO: support other indexes: GiST, SP-GiST, other user-defined idexes.
+ */
+int
+get_equal_strategy_number(Oid opclass)
+{
+	Oid am = get_opclass_method(opclass);
+
+	return get_equal_strategy_number_for_am(am);
+}
+
+
 /*
  * Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
  * is setup to match 'rel' (*NOT* idxrel!).
  *
  * Returns how many columns to use for the index scan.
  *
- * This is not generic routine, it expects the idxrel to be a btree, non-partial
- * and have at least one column reference (i.e. cannot consist of only
- * expressions).
+ * This is not generic routine, it expects the idxrel to be a btree or a hash,
+ * non-partial and have at least one column reference (i.e. cannot consist of
+ * only expressions).
  *
  * By definition, replication identity of a rel meets all limitations associated
  * with that. Note that any other index could also meet these limitations.
@@ -77,6 +131,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		Oid			opfamily;
 		RegProcedure regop;
 		int			table_attno = indkey->values[index_attoff];
+		int			eq_strategy;
 
 		if (!AttributeNumberIsValid(table_attno))
 		{
@@ -93,20 +148,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		 */
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
+		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
-									   BTEqualStrategyNumber);
+									   eq_strategy);
+
 		if (!OidIsValid(operator))
 			elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
-				 BTEqualStrategyNumber, optype, optype, opfamily);
+				 eq_strategy, optype, optype, opfamily);
 
 		regop = get_opcode(operator);
 
 		/* Initialize the scankey. */
 		ScanKeyInit(&skey[skey_attoff],
 					index_attoff + 1,
-					BTEqualStrategyNumber,
+					eq_strategy,
 					regop,
 					searchslot->tts_values[table_attno - 1]);
 
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 57ad22b48a..5f5da4c9d8 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -17,6 +17,7 @@
 
 #include "postgres.h"
 
+#include "access/amapi.h"
 #include "access/genam.h"
 #include "access/table.h"
 #include "catalog/namespace.h"
@@ -779,8 +780,8 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
 
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
- * the relation. The index must be btree, non-partial, and have at least
- * one column reference (i.e. cannot consist of only expressions). These
+ * the relation. The index must be btree or hash, non-partial, and have at
+ * least one column reference (i.e. cannot consist of only expressions). These
  * limitations help to keep the index scan similar to PK/RI index scans.
  *
  * Note that the limitations of index scans for replica identity full only
@@ -841,11 +842,25 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 bool
 IsIndexUsableForReplicaIdentityFull(IndexInfo *indexInfo)
 {
-	bool		is_btree = (indexInfo->ii_Am == BTREE_AM_OID);
-	bool		is_partial = (indexInfo->ii_Predicate != NIL);
-	bool		is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+	IndexAmRoutine *routine;
+	bool			is_suitable_type;
+	bool			is_partial;
+	bool			is_only_on_expression;
 
-	return is_btree && !is_partial && !is_only_on_expression;
+	/*
+	 * XXX: Support only indexes which implement amgettuple API. This is
+	 * because RelationFindReplTupleByIndex() assumes to be able to fetch
+	 * tuples one by one by the API.
+	 */
+	routine = GetIndexAmRoutineByAmId(indexInfo->ii_Am, false);
+	is_suitable_type = ((routine->amgettuple != NULL) &&
+						(get_equal_strategy_number_for_am(indexInfo->ii_Am)
+														!= InvalidStrategy));
+
+	is_partial = (indexInfo->ii_Predicate != NIL);
+	is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+ 
+ 	return is_suitable_type && !is_partial && !is_only_on_expression;
 }
 
 /*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 60978f9415..05813d98ae 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
 	return true;
 }
 
+/*
+ * get_opclass_method
+ *
+ *		Returns the OID of the index access method operator class is for.
+ */
+Oid
+get_opclass_method(Oid opclass)
+{
+	HeapTuple	tp;
+	Form_pg_opclass cla_tup;
+	Oid			result;
+
+	tp = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+	cla_tup = (Form_pg_opclass) GETSTRUCT(tp);
+
+	result = cla_tup->opcmethod;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*				---------- OPERATOR CACHE ----------					 */
 
 /*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..3a23510478 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -666,6 +666,9 @@ extern void CheckCmdReplicaIdentity(Relation rel, CmdType cmd);
 extern void CheckSubscriptionRelkind(char relkind, const char *nspname,
 									 const char *relname);
 
+extern int get_equal_strategy_number(Oid opclass);
+extern int get_equal_strategy_number_for_am(Oid am);
+
 /*
  * prototypes from functions in nodeModifyTable.c
  */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b972..ea8fbe42cd 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -106,6 +106,7 @@ extern Oid	get_opclass_family(Oid opclass);
 extern Oid	get_opclass_input_type(Oid opclass);
 extern bool get_opclass_opfamily_and_input_type(Oid opclass,
 												Oid *opfamily, Oid *opcintype);
+extern Oid get_opclass_method(Oid opclass);
 extern RegProcedure get_opcode(Oid opno);
 extern char *get_opname(Oid opno);
 extern Oid	get_op_rettype(Oid opno);
diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build
index bd673a9d68..5fa5f0a790 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -40,6 +40,7 @@ tests += {
       't/031_column_list.pl',
       't/032_subscribe_use_index.pl',
       't/033_run_as_table_owner.pl',
+      't/034_hash.pl',
       't/100_bugs.pl',
     ],
   },
diff --git a/src/test/subscription/t/034_hash.pl b/src/test/subscription/t/034_hash.pl
new file mode 100644
index 0000000000..3aead6958b
--- /dev/null
+++ b/src/test/subscription/t/034_hash.pl
@@ -0,0 +1,93 @@
+# Copyright (c) 2022-2023, PostgreSQL Global Development Group
+
+# Test logical replication behavior with subscriber using available index
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# create publisher node
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# create subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+my $appname = 'tap_sub';
+my $result = '';
+
+# =============================================================================
+# Testcase start: Subscription can use hash index
+#
+
+# create tables pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
+
+# insert some initial data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
+);
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# delete 2 rows
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
+
+# update 2 rows
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
+
+# wait until the index is used on the subscriber
+# XXX: the test will be suspended here
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+  )
+  or die
+  "Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
+
+# make sure that the subscriber has the correct data after the UPDATE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
+);
+is($result, qq(2),
+	'ensure subscriber has the correct data at the end of the test');
+
+# make sure that the subscriber has the correct data after the first DELETE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full where x in (5, 6)");
+is($result, qq(0),
+	'ensure subscriber has the correct data at the end of the test');
+
+# cleanup pub
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_rep_full");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+# cleanup sub
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_rep_full");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+
+# Testcase end: Subscription can use index
+# =============================================================================
+
+done_testing();
-- 
2.27.0

From 668d5589da6421687d95e0d5187077ebe66b9e78 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Date: Mon, 19 Jun 2023 08:28:00 +0000
Subject: [PATCH v3] Allow the use Hash index when REPLICA IDENTITY FULL is
 specified.

89e46d allowed using indexes other than PRIMARY KEY or REPLICA IDENTITY on the
subscriber, but only the BTree index could be used. This commit extends the
limitation, now the Hash index can be also used.

1) Other indexes do not have a fixed set of strategy numbers at all. In
build_replindex_scan_key(), the operator which corresponds to "equality comparison"
is searched by using the strategy number, but it is difficult for such indexes.
For example, GiST index operator classes for two-dimensional geometric objects have
a comparison "same", but its strategy number is different from the gist_int4_ops,
which is a GiST index operator class that implements the B-tree equivalent.

2) Some other indexes do not implement "amgettuple". RelationFindReplTupleByIndex()
assumes that tuples could be fetched one by one via index_getnext_slot(), but such
indexes are not supported.
---
 src/backend/executor/execReplication.c        | 79 +++++++++++++++++--
 src/backend/replication/logical/relation.c    | 19 +++--
 src/backend/utils/cache/lsyscache.c           | 22 ++++++
 src/include/executor/executor.h               |  1 +
 src/include/utils/lsyscache.h                 |  1 +
 .../subscription/t/032_subscribe_use_index.pl | 69 ++++++++++++++++
 6 files changed, 179 insertions(+), 12 deletions(-)

diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 9dd7168461..79ee7eca06 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -19,6 +19,7 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/pg_am_d.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
@@ -41,15 +42,78 @@
 static bool tuples_equal(TupleTableSlot *slot1, TupleTableSlot *slot2,
 						 TypeCacheEntry **eq);
 
+/*
+ * Return the strategy number which corresponds to the equality operator for
+ * given index access method.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. This is because
+ * mainly two reasons:
+ *
+ * 1) Other indexes do not have a fixed set of strategy numbers at all.
+ * In build_replindex_scan_key(), the operator which corresponds to "equality
+ * comparison" is searched by using the strategy number, but it is difficult
+ * for such indexes. For example, GiST index operator classes for
+ * two-dimensional geometric objects have a comparison "same", but its strategy
+ * number is different from the gist_int4_ops, which is a GiST index operator
+ * class that implements the B-tree equivalent.
+ *
+ * 2) Some other indexes do not implement "amgettuple".
+ * RelationFindReplTupleByIndex() assumes that tuples could be fetched one by
+ * one via index_getnext_slot(), but such indexes are not supported. To make it
+ * use index_getbitmap() must be used, but not done yet because of the above
+ * reason.
+ */
+int
+get_equal_strategy_number_for_am(Oid am)
+{
+	int			ret;
+
+	switch (am)
+	{
+		case BTREE_AM_OID:
+			ret = BTEqualStrategyNumber;
+			break;
+		case HASH_AM_OID:
+			ret = HTEqualStrategyNumber;
+			break;
+		case GIST_AM_OID:
+		case SPGIST_AM_OID:
+		case GIN_AM_OID:
+		case BRIN_AM_OID:
+		default:
+			/* XXX: Only Btree and Hash indexes are supported */
+			ret = InvalidStrategy;
+			break;
+	}
+
+	return ret;
+}
+
+/*
+ * Return the appropriate strategy number which corresponds to the equality
+ * operator.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. See comments
+ * atop get_equal_strategy_number_for_am().
+ */
+static int
+get_equal_strategy_number(Oid opclass)
+{
+	Oid			am = get_opclass_method(opclass);
+
+	return get_equal_strategy_number_for_am(am);
+}
+
+
 /*
  * Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
  * is setup to match 'rel' (*NOT* idxrel!).
  *
  * Returns how many columns to use for the index scan.
  *
- * This is not generic routine, it expects the idxrel to be a btree, non-partial
- * and have at least one column reference (i.e. cannot consist of only
- * expressions).
+ * This is not generic routine, it expects the idxrel to be a btree or a hash,
+ * non-partial and have at least one column reference (i.e. cannot consist of
+ * only expressions).
  *
  * By definition, replication identity of a rel meets all limitations associated
  * with that. Note that any other index could also meet these limitations.
@@ -77,6 +141,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		Oid			opfamily;
 		RegProcedure regop;
 		int			table_attno = indkey->values[index_attoff];
+		int			eq_strategy;
 
 		if (!AttributeNumberIsValid(table_attno))
 		{
@@ -93,20 +158,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		 */
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
+		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
-									   BTEqualStrategyNumber);
+									   eq_strategy);
+
 		if (!OidIsValid(operator))
 			elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
-				 BTEqualStrategyNumber, optype, optype, opfamily);
+				 eq_strategy, optype, optype, opfamily);
 
 		regop = get_opcode(operator);
 
 		/* Initialize the scankey. */
 		ScanKeyInit(&skey[skey_attoff],
 					index_attoff + 1,
-					BTEqualStrategyNumber,
+					eq_strategy,
 					regop,
 					searchslot->tts_values[table_attno - 1]);
 
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 57ad22b48a..3a4daf2669 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -779,8 +779,8 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
 
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
- * the relation. The index must be btree, non-partial, and have at least
- * one column reference (i.e. cannot consist of only expressions). These
+ * the relation. The index must be btree or hash, non-partial, and have at
+ * least one column reference (i.e. cannot consist of only expressions). These
  * limitations help to keep the index scan similar to PK/RI index scans.
  *
  * Note that the limitations of index scans for replica identity full only
@@ -841,11 +841,18 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 bool
 IsIndexUsableForReplicaIdentityFull(IndexInfo *indexInfo)
 {
-	bool		is_btree = (indexInfo->ii_Am == BTREE_AM_OID);
-	bool		is_partial = (indexInfo->ii_Predicate != NIL);
-	bool		is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+	bool		is_suitable_type;
+	bool		is_partial;
+	bool		is_only_on_expression;
 
-	return is_btree && !is_partial && !is_only_on_expression;
+	/* Check whether the index is supported or not */
+	is_suitable_type = ((get_equal_strategy_number_for_am(indexInfo->ii_Am)
+						 != InvalidStrategy));
+
+	is_partial = (indexInfo->ii_Predicate != NIL);
+	is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+
+	return is_suitable_type && !is_partial && !is_only_on_expression;
 }
 
 /*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 60978f9415..05813d98ae 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
 	return true;
 }
 
+/*
+ * get_opclass_method
+ *
+ *		Returns the OID of the index access method operator class is for.
+ */
+Oid
+get_opclass_method(Oid opclass)
+{
+	HeapTuple	tp;
+	Form_pg_opclass cla_tup;
+	Oid			result;
+
+	tp = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+	cla_tup = (Form_pg_opclass) GETSTRUCT(tp);
+
+	result = cla_tup->opcmethod;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*				---------- OPERATOR CACHE ----------					 */
 
 /*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..faa629c9fe 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -665,6 +665,7 @@ extern void CheckCmdReplicaIdentity(Relation rel, CmdType cmd);
 
 extern void CheckSubscriptionRelkind(char relkind, const char *nspname,
 									 const char *relname);
+extern int	get_equal_strategy_number_for_am(Oid am);
 
 /*
  * prototypes from functions in nodeModifyTable.c
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b972..f5fdbfe116 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -106,6 +106,7 @@ extern Oid	get_opclass_family(Oid opclass);
 extern Oid	get_opclass_input_type(Oid opclass);
 extern bool get_opclass_opfamily_and_input_type(Oid opclass,
 												Oid *opfamily, Oid *opcintype);
+extern Oid	get_opclass_method(Oid opclass);
 extern RegProcedure get_opcode(Oid opno);
 extern char *get_opname(Oid opno);
 extern Oid	get_op_rettype(Oid opno);
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index 576eec6a57..0e4e9ca18e 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -478,6 +478,75 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
 # data
 # =============================================================================
 
+# =============================================================================
+# Testcase start: Subscription can use hash index
+#
+
+# create tables pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
+
+# insert some initial data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
+);
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# delete 2 rows
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
+
+# update 2 rows
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
+
+# wait until the index is used on the subscriber
+# XXX: the test will be suspended here
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+  )
+  or die
+  "Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
+
+# make sure that the subscriber has the correct data after the UPDATE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
+);
+is($result, qq(2),
+	'ensure subscriber has the correct data at the end of the test');
+
+# make sure that the subscriber has the correct data after the first DELETE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full where x in (5, 6)");
+is($result, qq(0),
+	'ensure subscriber has the correct data at the end of the test');
+
+# cleanup pub
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_rep_full");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+# cleanup sub
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_rep_full");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+
+# Testcase end: Subscription can use hash index
+# =============================================================================
+
 $node_subscriber->stop('fast');
 $node_publisher->stop('fast');
 
-- 
2.27.0

From ac476d5417ffd75faa27b20e4d4ece4864c064f7 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Date: Mon, 19 Jun 2023 08:28:00 +0000
Subject: [PATCH v4] Allow the use Hash index when REPLICA IDENTITY FULL is
 specified.

89e46d allowed using indexes other than PRIMARY KEY or REPLICA IDENTITY on the
subscriber, but only the BTree index could be used. This commit extends the
limitation, now the Hash index can be also used. These 2 types of indexes are the
only ones supported because only these
- use fix strategy numbers
- implement the "equality" strategy
- implement the function amgettuple()

Other indexes do not have a fixed set of strategy numbers. In
build_replindex_scan_key(), the operator which corresponds to "equality comparison"
is searched by using the strategy number, but it is difficult for such indexes.
For example, GiST index operator classes for two-dimensional geometric objects have
a comparison "same", but its strategy number is different from the gist_int4_ops,
which is a GiST index operator class that implements the B-tree equivalent.

Moreover, BRIN and GIN indexes do not implement "amgettuple". RelationFindReplTupleByIndex()
assumes that tuples will be fetched one by one via index_getnext_slot(), but this
currently requires the "amgetuple" function.
---
 src/backend/executor/execReplication.c        | 74 +++++++++++++++++--
 src/backend/replication/logical/relation.c    | 19 +++--
 src/backend/utils/cache/lsyscache.c           | 22 ++++++
 src/include/executor/executor.h               |  1 +
 src/include/utils/lsyscache.h                 |  1 +
 .../subscription/t/032_subscribe_use_index.pl | 69 +++++++++++++++++
 6 files changed, 174 insertions(+), 12 deletions(-)

diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 9dd7168461..5f72133fca 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -19,6 +19,7 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/pg_am_d.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
@@ -41,15 +42,73 @@
 static bool tuples_equal(TupleTableSlot *slot1, TupleTableSlot *slot2,
 						 TypeCacheEntry **eq);
 
+/*
+ * Return the strategy number which corresponds to the equality operator for
+ * given index access method.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. This is because
+ * mainly two reasons:
+ *
+ * 1) Other indexes do not have a fixed set of strategy numbers.
+ * In build_replindex_scan_key(), the operator which corresponds to "equality
+ * comparison" is searched by using the strategy number, but it is difficult
+ * for such indexes. For example, GiST index operator classes for
+ * two-dimensional geometric objects have a comparison "same", but its strategy
+ * number is different from the gist_int4_ops, which is a GiST index operator
+ * class that implements the B-tree equivalent.
+ *
+ * 2) Moreover, BRIN and GIN indexes do not implement "amgettuple".
+ * RelationFindReplTupleByIndex() assumes that tuples will be fetched one by
+ * one via index_getnext_slot(), but this currently requires the "amgetuple"
+ * function. To make it use index_getbitmap() must be used, which fetches all
+ * the tuples at once.
+ */
+int
+get_equal_strategy_number_for_am(Oid am)
+{
+	int			ret;
+
+	switch (am)
+	{
+		case BTREE_AM_OID:
+			ret = BTEqualStrategyNumber;
+			break;
+		case HASH_AM_OID:
+			ret = HTEqualStrategyNumber;
+			break;
+		default:
+			/* XXX: Only Btree and Hash indexes are supported */
+			ret = InvalidStrategy;
+			break;
+	}
+
+	return ret;
+}
+
+/*
+ * Return the appropriate strategy number which corresponds to the equality
+ * operator.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. See comments
+ * atop get_equal_strategy_number_for_am().
+ */
+static int
+get_equal_strategy_number(Oid opclass)
+{
+	Oid			am = get_opclass_method(opclass);
+
+	return get_equal_strategy_number_for_am(am);
+}
+
 /*
  * Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
  * is setup to match 'rel' (*NOT* idxrel!).
  *
  * Returns how many columns to use for the index scan.
  *
- * This is not generic routine, it expects the idxrel to be a btree, non-partial
- * and have at least one column reference (i.e. cannot consist of only
- * expressions).
+ * This is not generic routine, it expects the idxrel to be a btree or a hash,
+ * non-partial and have at least one column reference (i.e. cannot consist of
+ * only expressions).
  *
  * By definition, replication identity of a rel meets all limitations associated
  * with that. Note that any other index could also meet these limitations.
@@ -77,6 +136,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		Oid			opfamily;
 		RegProcedure regop;
 		int			table_attno = indkey->values[index_attoff];
+		int			eq_strategy;
 
 		if (!AttributeNumberIsValid(table_attno))
 		{
@@ -93,20 +153,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		 */
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
+		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
-									   BTEqualStrategyNumber);
+									   eq_strategy);
+
 		if (!OidIsValid(operator))
 			elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
-				 BTEqualStrategyNumber, optype, optype, opfamily);
+				 eq_strategy, optype, optype, opfamily);
 
 		regop = get_opcode(operator);
 
 		/* Initialize the scankey. */
 		ScanKeyInit(&skey[skey_attoff],
 					index_attoff + 1,
-					BTEqualStrategyNumber,
+					eq_strategy,
 					regop,
 					searchslot->tts_values[table_attno - 1]);
 
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 57ad22b48a..3a4daf2669 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -779,8 +779,8 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
 
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
- * the relation. The index must be btree, non-partial, and have at least
- * one column reference (i.e. cannot consist of only expressions). These
+ * the relation. The index must be btree or hash, non-partial, and have at
+ * least one column reference (i.e. cannot consist of only expressions). These
  * limitations help to keep the index scan similar to PK/RI index scans.
  *
  * Note that the limitations of index scans for replica identity full only
@@ -841,11 +841,18 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 bool
 IsIndexUsableForReplicaIdentityFull(IndexInfo *indexInfo)
 {
-	bool		is_btree = (indexInfo->ii_Am == BTREE_AM_OID);
-	bool		is_partial = (indexInfo->ii_Predicate != NIL);
-	bool		is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+	bool		is_suitable_type;
+	bool		is_partial;
+	bool		is_only_on_expression;
 
-	return is_btree && !is_partial && !is_only_on_expression;
+	/* Check whether the index is supported or not */
+	is_suitable_type = ((get_equal_strategy_number_for_am(indexInfo->ii_Am)
+						 != InvalidStrategy));
+
+	is_partial = (indexInfo->ii_Predicate != NIL);
+	is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+
+	return is_suitable_type && !is_partial && !is_only_on_expression;
 }
 
 /*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 60978f9415..05813d98ae 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
 	return true;
 }
 
+/*
+ * get_opclass_method
+ *
+ *		Returns the OID of the index access method operator class is for.
+ */
+Oid
+get_opclass_method(Oid opclass)
+{
+	HeapTuple	tp;
+	Form_pg_opclass cla_tup;
+	Oid			result;
+
+	tp = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+	cla_tup = (Form_pg_opclass) GETSTRUCT(tp);
+
+	result = cla_tup->opcmethod;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*				---------- OPERATOR CACHE ----------					 */
 
 /*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..faa629c9fe 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -665,6 +665,7 @@ extern void CheckCmdReplicaIdentity(Relation rel, CmdType cmd);
 
 extern void CheckSubscriptionRelkind(char relkind, const char *nspname,
 									 const char *relname);
+extern int	get_equal_strategy_number_for_am(Oid am);
 
 /*
  * prototypes from functions in nodeModifyTable.c
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b972..f5fdbfe116 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -106,6 +106,7 @@ extern Oid	get_opclass_family(Oid opclass);
 extern Oid	get_opclass_input_type(Oid opclass);
 extern bool get_opclass_opfamily_and_input_type(Oid opclass,
 												Oid *opfamily, Oid *opcintype);
+extern Oid	get_opclass_method(Oid opclass);
 extern RegProcedure get_opcode(Oid opno);
 extern char *get_opname(Oid opno);
 extern Oid	get_op_rettype(Oid opno);
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index 576eec6a57..0e4e9ca18e 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -478,6 +478,75 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
 # data
 # =============================================================================
 
+# =============================================================================
+# Testcase start: Subscription can use hash index
+#
+
+# create tables pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
+
+# insert some initial data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
+);
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# delete 2 rows
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
+
+# update 2 rows
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
+
+# wait until the index is used on the subscriber
+# XXX: the test will be suspended here
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+  )
+  or die
+  "Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
+
+# make sure that the subscriber has the correct data after the UPDATE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
+);
+is($result, qq(2),
+	'ensure subscriber has the correct data at the end of the test');
+
+# make sure that the subscriber has the correct data after the first DELETE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full where x in (5, 6)");
+is($result, qq(0),
+	'ensure subscriber has the correct data at the end of the test');
+
+# cleanup pub
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_rep_full");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+# cleanup sub
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_rep_full");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+
+# Testcase end: Subscription can use hash index
+# =============================================================================
+
 $node_subscriber->stop('fast');
 $node_publisher->stop('fast');
 
-- 
2.27.0

From 3ba7e4e1f876d92a913015cdd21a2de32807cfc4 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Date: Mon, 19 Jun 2023 08:28:00 +0000
Subject: [PATCH v5] Allow the use Hash index when REPLICA IDENTITY FULL is
 specified.

89e46d allowed using indexes other than PRIMARY KEY or REPLICA IDENTITY on the
subscriber, but only the BTree index could be used. This commit extends the
limitation, now the Hash index can be also used. These 2 types of indexes are the
only ones supported because only these
- use fix strategy numbers
- implement the "equality" strategy
- supported by tuples_equal()
- implement the function amgettuple()

Index access methods other than them don't have a fixed strategy for equality
operation. Note that in other index access methods, the support routines of each
operator class interpret the strategy numbers according to the operator class's
definition. In build_replindex_scan_key(), the operator which corresponds to
"equality comparison" is searched by using the strategy number, but it is
difficult for such indexes.

tuples_equal() cannot accept a datatype that does not have an operator class for
Btree or Hash. One motivation for other types of indexes to be used is to define
an index to attributes such as datatypes like point and box. But lookup_type_cache(),
which is called from tuples_equal(), cannot return the valid value if input datatypes
do not have such a opclass.

Moreover, BRIN and GIN indexes do not implement "amgettuple". RelationFindReplTupleByIndex()
assumes that tuples will be fetched one by one via index_getnext_slot(), but this
currently requires the "amgettuple" function.
---
 doc/src/sgml/logical-replication.sgml         |  2 +-
 src/backend/executor/execReplication.c        | 64 +++++++++++++++--
 src/backend/replication/logical/relation.c    | 47 +++++++++++--
 src/backend/utils/cache/lsyscache.c           | 22 ++++++
 src/include/executor/executor.h               |  1 +
 src/include/utils/lsyscache.h                 |  1 +
 .../subscription/t/032_subscribe_use_index.pl | 69 +++++++++++++++++++
 7 files changed, 193 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index c5de2040f7..d8339add49 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -134,7 +134,7 @@
    to replica identity <literal>FULL</literal>, which means the entire row becomes
    the key.  When replica identity <literal>FULL</literal> is specified,
    indexes can be used on the subscriber side for searching the rows.  Candidate
-   indexes must be btree, non-partial, and have at least one column reference
+   indexes must be btree or hash, non-partial, and have at least one column reference
    (i.e. cannot consist of only expressions).  These restrictions
    on the non-unique index properties adhere to some of the restrictions that
    are enforced for primary keys.  If there are no such suitable indexes,
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 9dd7168461..6f8af59be1 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -19,6 +19,7 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/pg_am_d.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
@@ -41,15 +42,63 @@
 static bool tuples_equal(TupleTableSlot *slot1, TupleTableSlot *slot2,
 						 TypeCacheEntry **eq);
 
+/*
+ * Return the strategy number which corresponds to the equality operator for
+ * given index access method.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. This is because
+ * index access methods other than them don't have a fixed strategy for
+ * equality operation. Note that in other index access methods, the support
+ * routines of each operator class interpret the strategy numbers according to
+ * the operator class's definition. So, we return the InvalidStrategy number
+ * for index access methods other than BTREE and HASH.
+ */
+int
+get_equal_strategy_number_for_am(Oid am)
+{
+	int			ret;
+
+	switch (am)
+	{
+		case BTREE_AM_OID:
+			ret = BTEqualStrategyNumber;
+			break;
+		case HASH_AM_OID:
+			ret = HTEqualStrategyNumber;
+			break;
+		default:
+			/* XXX: Only Btree and Hash indexes are supported */
+			ret = InvalidStrategy;
+			break;
+	}
+
+	return ret;
+}
+
+/*
+ * Return the appropriate strategy number which corresponds to the equality
+ * operator.
+ *
+ * XXX: Currently, only Btree and Hash indexes are supported. See comments
+ * atop get_equal_strategy_number_for_am().
+ */
+static int
+get_equal_strategy_number(Oid opclass)
+{
+	Oid			am = get_opclass_method(opclass);
+
+	return get_equal_strategy_number_for_am(am);
+}
+
 /*
  * Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
  * is setup to match 'rel' (*NOT* idxrel!).
  *
  * Returns how many columns to use for the index scan.
  *
- * This is not generic routine, it expects the idxrel to be a btree, non-partial
- * and have at least one column reference (i.e. cannot consist of only
- * expressions).
+ * This is not generic routine, it expects the idxrel to be a btree or a hash,
+ * non-partial and have at least one column reference (i.e. cannot consist of
+ * only expressions).
  *
  * By definition, replication identity of a rel meets all limitations associated
  * with that. Note that any other index could also meet these limitations.
@@ -77,6 +126,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		Oid			opfamily;
 		RegProcedure regop;
 		int			table_attno = indkey->values[index_attoff];
+		int			eq_strategy;
 
 		if (!AttributeNumberIsValid(table_attno))
 		{
@@ -93,20 +143,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		 */
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
+		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
-									   BTEqualStrategyNumber);
+									   eq_strategy);
+
 		if (!OidIsValid(operator))
 			elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
-				 BTEqualStrategyNumber, optype, optype, opfamily);
+				 eq_strategy, optype, optype, opfamily);
 
 		regop = get_opcode(operator);
 
 		/* Initialize the scankey. */
 		ScanKeyInit(&skey[skey_attoff],
 					index_attoff + 1,
-					BTEqualStrategyNumber,
+					eq_strategy,
 					regop,
 					searchslot->tts_values[table_attno - 1]);
 
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 57ad22b48a..5f00a0caa9 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -17,6 +17,9 @@
 
 #include "postgres.h"
 
+#ifdef USE_ASSERT_CHECKING
+#include "access/amapi.h"
+#endif
 #include "access/genam.h"
 #include "access/table.h"
 #include "catalog/namespace.h"
@@ -779,8 +782,8 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
 
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
- * the relation. The index must be btree, non-partial, and have at least
- * one column reference (i.e. cannot consist of only expressions). These
+ * the relation. The index must be btree or hash, non-partial, and have at
+ * least one column reference (i.e. cannot consist of only expressions). These
  * limitations help to keep the index scan similar to PK/RI index scans.
  *
  * Note that the limitations of index scans for replica identity full only
@@ -837,15 +840,47 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 /*
  * Returns true if the index is usable for replica identity full. For details,
  * see FindUsableIndexForReplicaIdentityFull.
+ *
+ * XXX: Currently, only Btree and Hash indexes can be returned as usable one.
+ * This is because mainly two reasons:
+ *
+ * 1) Other index access methods other than Btree and Hash don't have a fixed
+ * strategy for equality operation. Refer comments atop
+ * get_equal_strategy_number_for_am.
+ * 2) tuples_equal cannot accept a datatype that does not have an operator
+ * class for Btree or Hash. One motivation for other types of indexes to be
+ * used is to define an index to attributes such as datatypes like point and
+ * box. But lookup_type_cache, which is called from tuples_equal, cannot return
+ * the valid value if input datatypes do not have such a opclass.
+ *
+ * Furthermore, BRIN and GIN indexes do not implement "amgettuple".
+ * RelationFindReplTupleByIndex assumes that tuples will be fetched one by
+ * one via index_getnext_slot, but this currently requires the "amgettuple"
+ * function.
  */
 bool
 IsIndexUsableForReplicaIdentityFull(IndexInfo *indexInfo)
 {
-	bool		is_btree = (indexInfo->ii_Am == BTREE_AM_OID);
-	bool		is_partial = (indexInfo->ii_Predicate != NIL);
-	bool		is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+	bool		is_partial;
+	bool		is_only_on_expression;
+
+	/* Check whether the index is supported or not */
+	if (get_equal_strategy_number_for_am(indexInfo->ii_Am) == InvalidStrategy)
+		return false;
+
+#ifdef USE_ASSERT_CHECKING
+	{
+		/* Check that the given index access method has amgettuple routine */
+		IndexAmRoutine *amroutine = GetIndexAmRoutineByAmId(indexInfo->ii_Am,
+															false);
+		Assert(amroutine->amgettuple != NULL);
+	}
+#endif
+
+	is_partial = (indexInfo->ii_Predicate != NIL);
+	is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
 
-	return is_btree && !is_partial && !is_only_on_expression;
+	return !is_partial && !is_only_on_expression;
 }
 
 /*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 60978f9415..2c01a86c29 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
 	return true;
 }
 
+/*
+ * get_opclass_method
+ *
+ *		Returns the OID of the index access method the opclass belongs to.
+ */
+Oid
+get_opclass_method(Oid opclass)
+{
+	HeapTuple	tp;
+	Form_pg_opclass cla_tup;
+	Oid			result;
+
+	tp = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+	cla_tup = (Form_pg_opclass) GETSTRUCT(tp);
+
+	result = cla_tup->opcmethod;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*				---------- OPERATOR CACHE ----------					 */
 
 /*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..faa629c9fe 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -665,6 +665,7 @@ extern void CheckCmdReplicaIdentity(Relation rel, CmdType cmd);
 
 extern void CheckSubscriptionRelkind(char relkind, const char *nspname,
 									 const char *relname);
+extern int	get_equal_strategy_number_for_am(Oid am);
 
 /*
  * prototypes from functions in nodeModifyTable.c
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b972..f5fdbfe116 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -106,6 +106,7 @@ extern Oid	get_opclass_family(Oid opclass);
 extern Oid	get_opclass_input_type(Oid opclass);
 extern bool get_opclass_opfamily_and_input_type(Oid opclass,
 												Oid *opfamily, Oid *opcintype);
+extern Oid	get_opclass_method(Oid opclass);
 extern RegProcedure get_opcode(Oid opno);
 extern char *get_opname(Oid opno);
 extern Oid	get_op_rettype(Oid opno);
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index 576eec6a57..0e4e9ca18e 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -478,6 +478,75 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
 # data
 # =============================================================================
 
+# =============================================================================
+# Testcase start: Subscription can use hash index
+#
+
+# create tables pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
+
+# insert some initial data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
+);
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# delete 2 rows
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
+
+# update 2 rows
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
+
+# wait until the index is used on the subscriber
+# XXX: the test will be suspended here
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+  )
+  or die
+  "Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
+
+# make sure that the subscriber has the correct data after the UPDATE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
+);
+is($result, qq(2),
+	'ensure subscriber has the correct data at the end of the test');
+
+# make sure that the subscriber has the correct data after the first DELETE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full where x in (5, 6)");
+is($result, qq(0),
+	'ensure subscriber has the correct data at the end of the test');
+
+# cleanup pub
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_rep_full");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+# cleanup sub
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_rep_full");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+
+# Testcase end: Subscription can use hash index
+# =============================================================================
+
 $node_subscriber->stop('fast');
 $node_publisher->stop('fast');
 
-- 
2.27.0

From 435c463cd7c30aecf51221c7ff9286b9a07328f6 Mon Sep 17 00:00:00 2001
From: Amit Kapila <akapila@postgresql.org>
Date: Thu, 13 Jul 2023 13:52:07 +0530
Subject: [PATCH v8] Allow the use of a hash index on the subscriber during
 replication.

Commit 89e46da5e5 allowed using BTREE indexes that are neither
PRIMARY KEY nor REPLICA IDENTITY on the subscriber during apply of
update/delete. This patch extends that functionality to use HASH indexes
as well.

We explored supporting other index access methods as well but they don't
have a fixed strategy for equality operation which is required by the
current infrastructure in logical replication to scan the indexes.

Author: Kuroda Hayato
Reviewed-by: Peter Smith, Onder Kalaci, Amit Kapila
Discussion: https://postgr.es/m/TYAPR01MB58669D7414E59664E17A5827F522A@TYAPR01MB5866.jpnprd01.prod.outlook.com
---
 doc/src/sgml/logical-replication.sgml         |  6 +-
 src/backend/executor/execReplication.c        | 53 ++++++++++++++-
 src/backend/replication/logical/relation.c    | 41 +++++++++--
 src/backend/utils/cache/lsyscache.c           | 22 ++++++
 src/include/executor/executor.h               |  1 +
 src/include/utils/lsyscache.h                 |  1 +
 .../subscription/t/032_subscribe_use_index.pl | 68 +++++++++++++++++++
 7 files changed, 181 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index c2a749d882..e71f4bac69 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -134,9 +134,9 @@
    to replica identity <literal>FULL</literal>, which means the entire row becomes
    the key.  When replica identity <literal>FULL</literal> is specified,
    indexes can be used on the subscriber side for searching the rows.  Candidate
-   indexes must be btree, non-partial, and the leftmost index field must be a
-   column (not an expression) that references the published table column.  These
-   restrictions on the non-unique index properties adhere to some of the
+   indexes must be btree or hash, non-partial, and the leftmost index field must
+   be a column (not an expression) that references the published table column.
+   These restrictions on the non-unique index properties adhere to some of the
    restrictions that are enforced for primary keys.  If there are no such
    suitable indexes, the search on the subscriber side can be very inefficient,
    therefore replica identity <literal>FULL</literal> should only be used as a
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index af09342881..e776524227 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -19,6 +19,7 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/pg_am_d.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
@@ -41,6 +42,49 @@
 static bool tuples_equal(TupleTableSlot *slot1, TupleTableSlot *slot2,
 						 TypeCacheEntry **eq);
 
+/*
+ * Returns the fixed strategy number, if any, of the equality operator for the
+ * given index access method, otherwise, InvalidStrategy.
+ *
+ * Currently, only Btree and Hash indexes are supported. The other index access
+ * methods don't have a fixed strategy for equality operation - instead, the
+ * support routines of each operator class interpret the strategy numbers
+ * according to the operator class's definition.
+ */
+StrategyNumber
+get_equal_strategy_number_for_am(Oid am)
+{
+	int			ret;
+
+	switch (am)
+	{
+		case BTREE_AM_OID:
+			ret = BTEqualStrategyNumber;
+			break;
+		case HASH_AM_OID:
+			ret = HTEqualStrategyNumber;
+			break;
+		default:
+			/* XXX: Only Btree and Hash indexes are supported */
+			ret = InvalidStrategy;
+			break;
+	}
+
+	return ret;
+}
+
+/*
+ * Return the appropriate strategy number which corresponds to the equality
+ * operator.
+ */
+static StrategyNumber
+get_equal_strategy_number(Oid opclass)
+{
+	Oid			am = get_opclass_method(opclass);
+
+	return get_equal_strategy_number_for_am(am);
+}
+
 /*
  * Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
  * is setup to match 'rel' (*NOT* idxrel!).
@@ -77,6 +121,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		Oid			opfamily;
 		RegProcedure regop;
 		int			table_attno = indkey->values[index_attoff];
+		StrategyNumber eq_strategy;
 
 		if (!AttributeNumberIsValid(table_attno))
 		{
@@ -93,20 +138,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		 */
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
+		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
-									   BTEqualStrategyNumber);
+									   eq_strategy);
+
 		if (!OidIsValid(operator))
 			elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
-				 BTEqualStrategyNumber, optype, optype, opfamily);
+				 eq_strategy, optype, optype, opfamily);
 
 		regop = get_opcode(operator);
 
 		/* Initialize the scankey. */
 		ScanKeyInit(&skey[skey_attoff],
 					index_attoff + 1,
-					BTEqualStrategyNumber,
+					eq_strategy,
 					regop,
 					searchslot->tts_values[table_attno - 1]);
 
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index c545b90636..e231a61334 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -17,6 +17,9 @@
 
 #include "postgres.h"
 
+#ifdef USE_ASSERT_CHECKING
+#include "access/amapi.h"
+#endif
 #include "access/genam.h"
 #include "access/table.h"
 #include "catalog/namespace.h"
@@ -779,7 +782,7 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
 
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
- * the relation. The index must be btree, non-partial, and the leftmost
+ * the relation. The index must be btree or hash, non-partial, and the leftmost
  * field must be a column (not an expression) that references the remote
  * relation column. These limitations help to keep the index scan similar
  * to PK/RI index scans.
@@ -834,15 +837,43 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 /*
  * Returns true if the index is usable for replica identity full. For details,
  * see FindUsableIndexForReplicaIdentityFull.
+ *
+ * Currently, only Btree and Hash indexes can be returned as usable. This
+ * is due to following reasons:
+ *
+ * 1) Other index access methods don't have a fixed strategy for equality
+ * operation. Refer get_equal_strategy_number_for_am().
+ *
+ * 2) For indexes other than PK and REPLICA IDENTITY, we need to match the
+ * local and remote tuples. The equality routine tuples_equal() cannot accept
+ * a datatype (e.g. point or box) that does not have an operator class for
+ * Btree or Hash.
+ *
+ * XXX: Note that BRIN and GIN indexes do not implement "amgettuple" which
+ * will be used later to fetch the tuples. See RelationFindReplTupleByIndex().
  */
 bool
 IsIndexUsableForReplicaIdentityFull(IndexInfo *indexInfo)
 {
-	bool		is_btree = (indexInfo->ii_Am == BTREE_AM_OID);
-	bool		is_partial = (indexInfo->ii_Predicate != NIL);
-	bool		is_only_on_expression = IsIndexOnlyOnExpression(indexInfo);
+	/* Ensure that the index access method has a valid equal strategy */
+	if (get_equal_strategy_number_for_am(indexInfo->ii_Am) == InvalidStrategy)
+		return false;
+	if (indexInfo->ii_Predicate != NIL)
+		return false;
+	if (IsIndexOnlyOnExpression(indexInfo))
+		return false;
+
+#ifdef USE_ASSERT_CHECKING
+	{
+		IndexAmRoutine *amroutine;
 
-	return is_btree && !is_partial && !is_only_on_expression;
+		/* The given index access method must implement amgettuple. */
+		amroutine = GetIndexAmRoutineByAmId(indexInfo->ii_Am, false);
+		Assert(amroutine->amgettuple != NULL);
+	}
+#endif
+
+	return true;
 }
 
 /*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 60978f9415..2c01a86c29 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
 	return true;
 }
 
+/*
+ * get_opclass_method
+ *
+ *		Returns the OID of the index access method the opclass belongs to.
+ */
+Oid
+get_opclass_method(Oid opclass)
+{
+	HeapTuple	tp;
+	Form_pg_opclass cla_tup;
+	Oid			result;
+
+	tp = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+	cla_tup = (Form_pg_opclass) GETSTRUCT(tp);
+
+	result = cla_tup->opcmethod;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*				---------- OPERATOR CACHE ----------					 */
 
 /*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..c677e490d7 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -646,6 +646,7 @@ extern void check_exclusion_constraint(Relation heap, Relation index,
 /*
  * prototypes from functions in execReplication.c
  */
+extern StrategyNumber get_equal_strategy_number_for_am(Oid am);
 extern bool RelationFindReplTupleByIndex(Relation rel, Oid idxoid,
 										 LockTupleMode lockmode,
 										 TupleTableSlot *searchslot,
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b972..f5fdbfe116 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -106,6 +106,7 @@ extern Oid	get_opclass_family(Oid opclass);
 extern Oid	get_opclass_input_type(Oid opclass);
 extern bool get_opclass_opfamily_and_input_type(Oid opclass,
 												Oid *opfamily, Oid *opcintype);
+extern Oid	get_opclass_method(Oid opclass);
 extern RegProcedure get_opcode(Oid opno);
 extern char *get_opname(Oid opno);
 extern Oid	get_op_rettype(Oid opno);
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index 576eec6a57..880ef2d57a 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -478,6 +478,74 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
 # data
 # =============================================================================
 
+# =============================================================================
+# Testcase start: Subscription can use hash index
+#
+
+# create tables on pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_replica_id_full (x int, y text)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
+
+# insert some initial data
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
+);
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# delete 2 rows
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
+
+# update 2 rows
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
+
+# wait until the index is used on the subscriber
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+  )
+  or die
+  "Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
+
+# make sure that the subscriber has the correct data after the UPDATE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
+);
+is($result, qq(2),
+	'ensure subscriber has the correct data at the end of the test');
+
+# make sure that the subscriber has the correct data after the first DELETE
+$result = $node_subscriber->safe_psql('postgres',
+	"select count(*) from test_replica_id_full where x in (5, 6)");
+is($result, qq(0),
+	'ensure subscriber has the correct data at the end of the test');
+
+# cleanup pub
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_rep_full");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+# cleanup sub
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_rep_full");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
+
+# Testcase end: Subscription can use hash index
+# =============================================================================
+
 $node_subscriber->stop('fast');
 $node_publisher->stop('fast');
 
-- 
2.39.1