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
