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?
