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

Dear Önder,

Thank you for your analysis!

>
Yes, I agree, it is (and was before my patch as well) un-documented limitation of REPLICA IDENTITY FULL.
And, as far as I can see, my patch actually didn't have any impact on the limitation. The unsupported
cases are still unsupported, but now the same error is thrown in a slightly different place.
I think that is a minor limitation, but maybe should be listed [1]?
>

Yes, your modification did not touch the restriction. It has existed before the
commit. I (or my colleague) will post the patch to add the description, maybe
after [1] is committed.

>
For this one, I did some research in the code, but  I'm not very
comfortable with the answer. Still, I wanted to share my observations so
that it might be useful for the discussion.

First, I checked if the function get_op_btree_interpretation() could be
used here. But, I think that is again btree-only and I couldn't find
anything generic that does something similar.
>

Thanks for checking. The function seems to return the list of operator family and
its strategy number when the oid of the operator is given. But what we want to do
here is get the operator oid. I think that the input and output of the function
seems opposite. And as you mentioned, the index must be btree.

>
Then, I tried to come up with a SQL query, actually based on the link [2]
you shared. I think we should always have an "equality" strategy (e.g.,
"same", "overlaps", "contains" etc sounds wrong to me).
>

I could agree that "overlaps", "contains", are not "equal", but not sure about
the "same". Around here we must discuss, but not now.

>
And, it seems btree, hash and brin supports "equal". So, a query like the
following seems to provide the list of (index type, strategy_number,
data_type) that we might be allowed to use.
>

Note that strategy numbers listed in the doc are just an example - Other than BTree
and Hash do not have a fixed set of strategies at all.
E.g., operator classes for Btree, Hash and BRIN (Minmax) has "equal" and the
strategy number is documented. But other user-defined operator classes for BRIN
may have another number, or it does not have equality comparison.

>
  SELECT
    am.amname AS index_type,
 amop.amoplefttype::regtype,amop.amoprighttype::regtype,
    op.oprname AS operator,
    amop.amopstrategy AS strategy_number
FROM
    pg_amop amop
JOIN
    pg_am am ON am.oid = amop.amopmethod
JOIN
    pg_operator op ON op.oid = amop.amopopr
WHERE
    (am.amname = 'btree' and amop.amopstrategy = 3) OR
    (am.amname = 'hash' and amop.amopstrategy = 1) OR
    (am.amname = 'brin' and amop.amopstrategy = 3)
ORDER BY
    index_type,
    strategy_number;

What do you think?
>

Good SQL. You have listed the equality operator and related strategy number for given
operator classes.

While analyzing more, however, I found that it might be difficult to support GIN, BRIN,
and bloom indexes in the first version. These indexes does not implement the
"amgettuple" function, which is called in RelationFindReplTupleByIndex()->index_getnext_slot()->index_getnext_tid().
For example, in the brinhandler():

```
/*
 * BRIN handler function: return IndexAmRoutine with access method parameters
 * and callbacks.
 */
Datum
brinhandler(PG_FUNCTION_ARGS)
{
...
        amroutine->amgettuple = NULL;
        amroutine->amgetbitmap = bringetbitmap;
...
```

According to the document [2], all of index access methods must implement either
of amgettuple or amgetbitmap  API. "amgettuple" is used when the table is scaned
and tuples are fetched one by one, RelationFindReplTupleByIndex() do that.
"amgetbitmap" is used when all tuples are fetched at once and RelationFindReplTupleByIndex()
does not support such indexes. To do that the implemented API must be checked and
the function must be changed depends on that. It may be difficult to add them in
the first step so that I want not to support them. Fortunately, Hash, GiST, and
SP-GiST has implemented then so we can focus on them.
In the next patch I will add the mechanism for rejecting such indexes.

Anyway, thank you for keeping the interest to the patch, nevertheless it is difficult theme.

[1]: https://www.postgresql.org/message-id/CAHut%2BPsFdTZJ7DG1jyu7BpA_1d4hwEd-Q%2BmQAPWcj1ZLD_X5Dw%40mail.gmail.com
[2]: https://www.postgresql.org/docs/current/index-functions.html

Best Regards,
Hayato Kuroda
FUJITSU LIMITED