Why it is not possible to create custom AM which behaves similar to btree?

Started by Konstantin Knizhnikover 5 years ago3 messages
#1Konstantin Knizhnik
k.knizhnik@postgrespro.ru

Hi hackers.

I tried to create LSM AM which can be used instead of nbtree.
I looked at contrib/btree/gin, contrib/isn and try to do the following:

CREATE OPERATOR FAMILY lsm3_float_ops USING lsm3;

CREATE OPERATOR CLASS float4_ops DEFAULT
    FOR TYPE float4 USING lsm3 FAMILY lsm3_float_ops AS
    OPERATOR 1  <,
    OPERATOR 2  <=,
    OPERATOR 3  =,
    OPERATOR 4  >=,
    OPERATOR 5  >,
    FUNCTION 1  btfloat4cmp(float4,float4);

CREATE OPERATOR CLASS float8_ops DEFAULT
    FOR TYPE float8 USING lsm3 FAMILY lsm3_float_ops AS
    OPERATOR 1  <,
    OPERATOR 2  <=,
    OPERATOR 3  =,
    OPERATOR 4  >=,
    OPERATOR 5  >,
    FUNCTION 1  btfloat8cmp(float8,float8);

ALTER OPERATOR FAMILY lsm3_float_ops USING lsm3 ADD
    OPERATOR 1  < (float4,float8),
    OPERATOR 1  < (float8,float4),

    OPERATOR 2  <= (float4,float8),
    OPERATOR 2  <= (float8,float4),

    OPERATOR 3  = (float4,float8),
    OPERATOR 3  = (float8,float4),

    OPERATOR 4  >= (float4,float8),
    OPERATOR 4  >= (float8,float4),

    OPERATOR 5  > (float4,float8),
    OPERATOR 5  > (float8,float4),

    FUNCTION 1  btfloat48cmp(float4,float8),
    FUNCTION 1  btfloat84cmp(float8,float4);

But then I get error for btfloat48cmp and btfloat84cmp functions:

ERROR:  associated data types must be specified for index support function

If I replace lsm3 with btree in ALTER FAMILY, then there is no error.
I wonder if it is possible in Postgres to define custom index, which can
handle comparison of different types, i.e.

create table t(pk bigint);
create index on t using lsm3(pk);
select * from t where pk=1;

I failed to make Postgres use index in this case. Index is used only if
I rewrite this query in this way:
select * from t where pk=1::bigint;

Thanks in advance,
Konstantin

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Konstantin Knizhnik (#1)
Re: Why it is not possible to create custom AM which behaves similar to btree?

Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:

But then I get error for btfloat48cmp and btfloat84cmp functions:
ERROR: associated data types must be specified for index support function

You need to specify the amproclefttype and amprocrighttype types you
want the function to be registered under. The core code knows that
for btree, those are the same as the actual parameter types of the
function; but there's no reason to make such an assumption for other AMs.
So you have to write it out; perhaps

...
FUNCTION 1(float4,float8) btfloat48cmp(float4,float8),
...

regards, tom lane

#3Konstantin Knizhnik
k.knizhnik@postgrespro.ru
In reply to: Tom Lane (#2)
Re: Why it is not possible to create custom AM which behaves similar to btree?

On 23.07.2020 03:11, Tom Lane wrote:

Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:

But then I get error for btfloat48cmp and btfloat84cmp functions:
ERROR: associated data types must be specified for index support function

You need to specify the amproclefttype and amprocrighttype types you
want the function to be registered under. The core code knows that
for btree, those are the same as the actual parameter types of the
function; but there's no reason to make such an assumption for other AMs.
So you have to write it out; perhaps

...
FUNCTION 1(float4,float8) btfloat48cmp(float4,float8),
...

Thank you very much.
It works!