Обсуждение: Why it is not possible to create custom AM which behaves similar to btree?
Why it is not possible to create custom AM which behaves similar to btree?
От
Konstantin Knizhnik
Дата:
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
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
Re: Why it is not possible to create custom AM which behaves similar to btree?
От
Konstantin Knizhnik
Дата:
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!