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

Hi Hayato,

> BTW, I have doubt that the restriction is not related with your commit.
> In other words, if the table has attributes which the datatype is not for
> operator
> class of Btree, we could not use REPLICA IDENTITY FULL. IIUC it is not
> documented.
> Please see attched script to reproduce that. The final DELETE statement
> cannot be
> replicated at the subscriber on my env.
>
>
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]?

>
> For the specific notes you raised about strategy numbers / operator
> classes, I need to
> study a bit :) Though, I'll be available to do that early next week.
> >
>
> Thanks! I'm looking forward to see your opinions...
>

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.

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).

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.

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?

[1]
https://www.postgresql.org/docs/current/logical-replication-restrictions.html

[2] https://www.postgresql.org/docs/devel/xindex.html#XINDEX-STRATEGIES