Dear hackers, (CC: Önder because he owned the related thread) This is a follow-up thread of [1]. The commit allowed subscribers to use indexes other than PK and REPLICA IDENTITY when REPLICA IDENTITY is FULL on publisher, but the index must be a B-tree. In this proposal, I aim to extend this functionality to allow for hash indexes and potentially other types. I would like to share an initial patch to activate discussions.
# Current problem
The current limitation comes from the function build_replindex_scan_key(),
specifically these lines:
```
/*
* Load the operator info. We need this to get the equality
operator
* function for the scan key.
*/
optype = get_opclass_input_type(opclass->values[index_attoff]);
opfamily = get_opclass_family(opclass->values[index_attoff]);
operator = get_opfamily_member(opfamily, optype,
optype,
BTEqualStrategyNumber);
```
These lines retrieve an operator for equality comparisons. The "strategy number"
[2] identifies this operator. For B-tree indexes, an equal-comparison operator
is always associated with a fixed number (BTEqualStrategyNumber, 3). However,
this approach fails for other types of indexes because the strategy number is
determined by the operator class, not fixed.
# Proposed solution
I propose a patch that chooses the correct strategy number based on the index
access method. We would extract the access method from the pg_opclass system
catalog, similar to the approach used for data types and operator families.
Also, this patch change the condition for using the index on the subscriber
(see IsIndexUsableForReplicaIdentityFull()).
However, this solution does not yet extend to GiST, SP-GiST, GIN, BRIN due to
implementation constraints.
## Current difficulties
The challenge with supporting other indexes is that they lack a fixed set of
strategies,
making it difficult to choose the correct strategy number based on the index
access method. Even within the same index type, different operator classes can
use different strategy numbers for the same operation.
E.g. [2] shows that number 6 can be used for the purpose, but other operator
classes
added by btree_gist [3] seem to use number 3 for the euqlaity comparison.
I've looked into using ExecIndexBuildScanKeys(), which is used for normal index
scans.
However, in this case, the operator and its family seem to be determined by the
planner.
Based on that, the associated strategy number is extracted. This is the opposite
of what I am trying to achieve, so it doesn't seem helpful in this context.
The current patch only includes tests for hash indexes. These are separated into
the file 032_subscribe_use_index.pl for convenience, but will be integrated in a
later version.
How do you think? I want to know your opinion.
[1]:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=89e46da5e511a6970e26a020f265c9fb4b72b1d2
[2]: https://www.postgresql.org/docs/devel/xindex.html#XINDEX-STRATEGIES
[3]: https://www.postgresql.org/docs/devel/btree-gist.html
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
0001-Allow-to-use-Hash-index-on-subscriber.patch
Description: 0001-Allow-to-use-Hash-index-on-subscriber.patch
