Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXCxt-00077i-Qy for pgsql-bugs@arkaria.postgresql.org; Tue, 19 Nov 2019 23:31:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iXCxs-0000h3-Jg for pgsql-bugs@arkaria.postgresql.org; Tue, 19 Nov 2019 23:31:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXCxs-0000gw-AU for pgsql-bugs@lists.postgresql.org; Tue, 19 Nov 2019 23:31:20 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXCxp-0008Ca-EW for pgsql-bugs@lists.postgresql.org; Tue, 19 Nov 2019 23:31:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xAJNVBZG027667; Tue, 19 Nov 2019 18:31:11 -0500 From: Tom Lane To: Manuel Rigger cc: PostgreSQL mailing lists Subject: Re: No = operator for opfamily 426 In-reply-to: <8686.1574178454@sss.pgh.pa.us> References: <8686.1574178454@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 19 Nov 2019 10:47:34 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <27604.1574206172.0@sss.pgh.pa.us> Date: Tue, 19 Nov 2019 18:31:11 -0500 Message-ID: <27666.1574206271@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <27604.1574206172.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable I wrote: > Manuel Rigger writes: >> Consider the following statements: >> CREATE TABLE t0(c0 TEXT); >> CREATE INDEX i0 ON t0(c0 bpchar_ops); >> SELECT * FROM t0 WHERE t0.c0 LIKE ''; -- ERROR: no =3D operator for op= family 426 > Really what the error is showing is that like_support.c is being too > aggressive by assuming that it'll necessarily find a matching opfamily > member. It should probably just silently fail if it can't construct > the opclause it wants. I pushed a stopgap fix that just does that, but I think really what we ought to do about this is decouple like_support.c as far as possible from the index opclass. The notion that we choose the target operators based on the opclass is really backwards now that I think about it. The operators that represent the potentially indexscannable conditions are determined by the LIKE/regex operator, and what we should do is just ask whether the opclass can support them. The "pattern" opclasses put a crimp in this position, but those can now be seen to be legacy things not a model that future code is likely to follow. So I present the attached proposed patch that does things this way. The only short-term advantage is that it can handle applying an exact-match LIKE to a hash opclass: regression=3D# create table t (f1 text); CREATE TABLE regression=3D# create index on t using hash(f1); CREATE INDEX regression=3D# explain select * from t where f1 like 'foo'; QUERY PLAN = ----------------------------------------------------------------------- Bitmap Heap Scan on t (cost=3D4.05..14.20 rows=3D7 width=3D32) Filter: (f1 ~~ 'foo'::text) -> Bitmap Index Scan on t_f1_idx (cost=3D0.00..4.05 rows=3D7 width=3D= 0) Index Cond: (f1 =3D 'foo'::text) (4 rows) which isn't much of a gain, admittedly. But now this code won't need revision when we start to think about new index AMs with new opclasses that happen to implement btree-ish semantics. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="revise-pattern-match-indexqual-expansion.patch"; charset="us-ascii" Content-ID: <27604.1574206172.2@sss.pgh.pa.us> Content-Description: revise-pattern-match-indexqual-expansion.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/= like_support.c index 77cc378..e785435 100644 --- a/src/backend/utils/adt/like_support.c +++ b/src/backend/utils/adt/like_support.c @@ -39,6 +39,7 @@ #include "access/htup_details.h" #include "access/stratnum.h" #include "catalog/pg_collation.h" +#include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" #include "catalog/pg_statistic.h" #include "catalog/pg_type.h" @@ -240,7 +241,10 @@ match_pattern_prefix(Node *leftop, Pattern_Prefix_Status pstatus; Oid ldatatype; Oid rdatatype; - Oid oproid; + Oid eqopr; + Oid ltopr; + Oid geopr; + bool collation_aware; Expr *expr; FmgrInfo ltproc; Const *greaterstr; @@ -284,62 +288,89 @@ match_pattern_prefix(Node *leftop, return NIL; = /* - * Must also check that index's opfamily supports the operators we will - * want to apply. (A hash index, for example, will not support ">=3D".) - * Currently, only btree and spgist support the operators we need. - * - * Note: actually, in the Pattern_Prefix_Exact case, we only need "=3D" = so a - * hash index would work. Currently it doesn't seem worth checking for - * that, however. - * - * We insist on the opfamily being one of the specific ones we expect, - * else we'd do the wrong thing if someone were to make a reverse-sort - * opfamily with the same operators. - * - * The non-pattern opclasses will not sort the way we need in most non-C - * locales. We can use such an index anyway for an exact match (simple - * equality), but not for prefix-match cases. Note that here we are - * looking at the index's collation, not the expression's collation -- - * this test is *not* dependent on the LIKE/regex operator's collation. - * - * While we're at it, identify the type the comparison constant(s) shoul= d - * have, based on the opfamily. + * Identify the operators we want to use, based on the type of the + * left-hand argument. Usually these are just the type's regular + * comparison operators, but if we are considering one of the semi-legac= y + * "pattern" opclasses, use the "pattern" operators instead. Those are + * not collation-sensitive but always use C collation, as we want. The + * selected operators also determine the needed type of the prefix + * constant. */ - switch (opfamily) + ldatatype =3D exprType(leftop); + switch (ldatatype) { - case TEXT_BTREE_FAM_OID: - if (!(pstatus =3D=3D Pattern_Prefix_Exact || - lc_collate_is_c(indexcollation))) - return NIL; + case TEXTOID: + if (opfamily =3D=3D TEXT_PATTERN_BTREE_FAM_OID || + opfamily =3D=3D TEXT_SPGIST_FAM_OID) + { + eqopr =3D TextEqualOperator; + ltopr =3D TextPatternLessOperator; + geopr =3D TextPatternGreaterEqualOperator; + collation_aware =3D false; + } + else + { + eqopr =3D TextEqualOperator; + ltopr =3D TextLessOperator; + geopr =3D TextGreaterEqualOperator; + collation_aware =3D true; + } rdatatype =3D TEXTOID; break; + case NAMEOID: = - case TEXT_PATTERN_BTREE_FAM_OID: - case TEXT_SPGIST_FAM_OID: + /* + * Note that here, we need the RHS type to be text, so that the + * comparison value isn't improperly truncated to NAMEDATALEN. + */ + eqopr =3D NameEqualTextOperator; + ltopr =3D NameLessTextOperator; + geopr =3D NameGreaterEqualTextOperator; + collation_aware =3D true; rdatatype =3D TEXTOID; break; - - case BPCHAR_BTREE_FAM_OID: - if (!(pstatus =3D=3D Pattern_Prefix_Exact || - lc_collate_is_c(indexcollation))) - return NIL; - rdatatype =3D BPCHAROID; - break; - - case BPCHAR_PATTERN_BTREE_FAM_OID: + case BPCHAROID: + if (opfamily =3D=3D BPCHAR_PATTERN_BTREE_FAM_OID) + { + eqopr =3D BpcharEqualOperator; + ltopr =3D BpcharPatternLessOperator; + geopr =3D BpcharPatternGreaterEqualOperator; + collation_aware =3D false; + } + else + { + eqopr =3D BpcharEqualOperator; + ltopr =3D BpcharLessOperator; + geopr =3D BpcharGreaterEqualOperator; + collation_aware =3D true; + } rdatatype =3D BPCHAROID; break; - - case BYTEA_BTREE_FAM_OID: + case BYTEAOID: + eqopr =3D ByteaEqualOperator; + ltopr =3D ByteaLessOperator; + geopr =3D ByteaGreaterEqualOperator; + collation_aware =3D false; rdatatype =3D BYTEAOID; break; - default: + /* Can't get here unless we're attached to the wrong operator */ return NIL; } = - /* OK, prepare to create the indexqual(s) */ - ldatatype =3D exprType(leftop); + /* + * If necessary, verify that the index's collation behavior is compatibl= e. + * For an exact-match case, we don't have to be picky. Otherwise, insis= t + * that the index collation be "C". Note that here we are looking at th= e + * index's collation, not the expression's collation -- this test is *no= t* + * dependent on the LIKE/regex operator's collation. + */ + if (collation_aware) + { + if (!(pstatus =3D=3D Pattern_Prefix_Exact || + lc_collate_is_c(indexcollation))) + return NIL; + } = /* * If necessary, coerce the prefix constant to the right type. The give= n @@ -358,16 +389,17 @@ match_pattern_prefix(Node *leftop, /* * If we found an exact-match pattern, generate an "=3D" indexqual. * - * (Despite the checks above, we might fail to find a suitable operator = in - * some cases with binary-compatible opclasses. Just punt if so.) + * Here and below, check to see whether the desired operator is actually + * supported by the index opclass, and fail quietly if not. This allows + * us to not be concerned with specific opclasses (except for the legacy + * "pattern" cases); any index that correctly implements the operators + * will work. */ if (pstatus =3D=3D Pattern_Prefix_Exact) { - oproid =3D get_opfamily_member(opfamily, ldatatype, rdatatype, - BTEqualStrategyNumber); - if (oproid =3D=3D InvalidOid) + if (!op_in_opfamily(eqopr, opfamily)) return NIL; - expr =3D make_opclause(oproid, BOOLOID, false, + expr =3D make_opclause(eqopr, BOOLOID, false, (Expr *) leftop, (Expr *) prefix, InvalidOid, indexcollation); result =3D list_make1(expr); @@ -379,11 +411,9 @@ match_pattern_prefix(Node *leftop, * * We can always say "x >=3D prefix". */ - oproid =3D get_opfamily_member(opfamily, ldatatype, rdatatype, - BTGreaterEqualStrategyNumber); - if (oproid =3D=3D InvalidOid) + if (!op_in_opfamily(geopr, opfamily)) return NIL; - expr =3D make_opclause(oproid, BOOLOID, false, + expr =3D make_opclause(geopr, BOOLOID, false, (Expr *) leftop, (Expr *) prefix, InvalidOid, indexcollation); result =3D list_make1(expr); @@ -396,15 +426,13 @@ match_pattern_prefix(Node *leftop, * using a C-locale index collation. *------- */ - oproid =3D get_opfamily_member(opfamily, ldatatype, rdatatype, - BTLessStrategyNumber); - if (oproid =3D=3D InvalidOid) + if (!op_in_opfamily(ltopr, opfamily)) return result; - fmgr_info(get_opcode(oproid), <proc); + fmgr_info(get_opcode(ltopr), <proc); greaterstr =3D make_greater_string(prefix, <proc, indexcollation); if (greaterstr) { - expr =3D make_opclause(oproid, BOOLOID, false, + expr =3D make_opclause(ltopr, BOOLOID, false, (Expr *) leftop, (Expr *) greaterstr, InvalidOid, indexcollation); result =3D lappend(result, expr); diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_= operator.dat index fa7dc96..ba9c09d 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -107,12 +107,12 @@ oprcode =3D> 'starts_with', oprrest =3D> 'prefixsel', oprjoin =3D> 'prefixjoinsel' }, = -{ oid =3D> '254', descr =3D> 'equal', +{ oid =3D> '254', oid_symbol =3D> 'NameEqualTextOperator', descr =3D> 'e= qual', oprname =3D> '=3D', oprcanmerge =3D> 't', oprcanhash =3D> 't', oprleft = =3D> 'name', oprright =3D> 'text', oprresult =3D> 'bool', oprcom =3D> '=3D(text,name= )', oprnegate =3D> '<>(name,text)', oprcode =3D> 'nameeqtext', oprrest =3D>= 'eqsel', oprjoin =3D> 'eqjoinsel' }, -{ oid =3D> '255', descr =3D> 'less than', +{ oid =3D> '255', oid_symbol =3D> 'NameLessTextOperator', descr =3D> 'les= s than', oprname =3D> '<', oprleft =3D> 'name', oprright =3D> 'text', oprresult = =3D> 'bool', oprcom =3D> '>(text,name)', oprnegate =3D> '>=3D(name,text)', oprcode =3D> 'namelttext', oprrest =3D> 'scalarltsel', @@ -122,7 +122,7 @@ oprcom =3D> '>=3D(text,name)', oprnegate =3D> '>(name,text)', oprcode =3D> 'nameletext', oprrest =3D> 'scalarlesel', oprjoin =3D> 'scalarlejoinsel' }, -{ oid =3D> '257', descr =3D> 'greater than or equal', +{ oid =3D> '257', oid_symbol =3D> 'NameGreaterEqualTextOperator', descr =3D= > 'greater than or equal', oprname =3D> '>=3D', oprleft =3D> 'name', oprright =3D> 'text', oprresu= lt =3D> 'bool', oprcom =3D> '<=3D(text,name)', oprnegate =3D> '<(name,text)', oprcode =3D> 'namegetext', oprrest =3D> 'scalargesel', @@ -789,7 +789,7 @@ oprname =3D> '>=3D', oprleft =3D> 'name', oprright =3D> 'name', oprresu= lt =3D> 'bool', oprcom =3D> '<=3D(name,name)', oprnegate =3D> '<(name,name)', oprcode =3D= > 'namege', oprrest =3D> 'scalargesel', oprjoin =3D> 'scalargejoinsel' }, -{ oid =3D> '664', descr =3D> 'less than', +{ oid =3D> '664', oid_symbol =3D> 'TextLessOperator', descr =3D> 'less th= an', oprname =3D> '<', oprleft =3D> 'text', oprright =3D> 'text', oprresult = =3D> 'bool', oprcom =3D> '>(text,text)', oprnegate =3D> '>=3D(text,text)', oprcode =3D= > 'text_lt', oprrest =3D> 'scalarltsel', oprjoin =3D> 'scalarltjoinsel' }, @@ -801,7 +801,7 @@ oprname =3D> '>', oprleft =3D> 'text', oprright =3D> 'text', oprresult = =3D> 'bool', oprcom =3D> '<(text,text)', oprnegate =3D> '<=3D(text,text)', oprcode =3D= > 'text_gt', oprrest =3D> 'scalargtsel', oprjoin =3D> 'scalargtjoinsel' }, -{ oid =3D> '667', descr =3D> 'greater than or equal', +{ oid =3D> '667', oid_symbol =3D> 'TextGreaterEqualOperator', descr =3D> = 'greater than or equal', oprname =3D> '>=3D', oprleft =3D> 'text', oprright =3D> 'text', oprresu= lt =3D> 'bool', oprcom =3D> '<=3D(text,text)', oprnegate =3D> '<(text,text)', oprcode =3D= > 'text_ge', oprrest =3D> 'scalargesel', oprjoin =3D> 'scalargejoinsel' }, @@ -1160,7 +1160,7 @@ oprname =3D> '@@', oprkind =3D> 'l', oprleft =3D> '0', oprright =3D> 'p= olygon', oprresult =3D> 'point', oprcode =3D> 'poly_center' }, = -{ oid =3D> '1054', descr =3D> 'equal', +{ oid =3D> '1054', oid_symbol =3D> 'BpcharEqualOperator', descr =3D> 'equ= al', oprname =3D> '=3D', oprcanmerge =3D> 't', oprcanhash =3D> 't', oprleft = =3D> 'bpchar', oprright =3D> 'bpchar', oprresult =3D> 'bool', oprcom =3D> '=3D(bpchar,= bpchar)', oprnegate =3D> '<>(bpchar,bpchar)', oprcode =3D> 'bpchareq', oprrest =3D= > 'eqsel', @@ -1180,7 +1180,7 @@ oprresult =3D> 'bool', oprcom =3D> '<>(bpchar,bpchar)', oprnegate =3D> '=3D(bpchar,bpchar)', oprcode =3D> 'bpcharne', oprrest =3D= > 'neqsel', oprjoin =3D> 'neqjoinsel' }, -{ oid =3D> '1058', descr =3D> 'less than', +{ oid =3D> '1058', oid_symbol =3D> 'BpcharLessOperator', descr =3D> 'less= than', oprname =3D> '<', oprleft =3D> 'bpchar', oprright =3D> 'bpchar', oprresult =3D> 'bool', oprcom =3D> '>(bpchar,bpchar)', oprnegate =3D> '>=3D(bpchar,bpchar)', oprcode =3D> 'bpcharlt', @@ -1195,7 +1195,7 @@ oprresult =3D> 'bool', oprcom =3D> '<(bpchar,bpchar)', oprnegate =3D> '<=3D(bpchar,bpchar)', oprcode =3D> 'bpchargt', oprrest =3D> 'scalargtsel', oprjoin =3D> 'scalargtjoinsel' }, -{ oid =3D> '1061', descr =3D> 'greater than or equal', +{ oid =3D> '1061', oid_symbol =3D> 'BpcharGreaterEqualOperator', descr =3D= > 'greater than or equal', oprname =3D> '>=3D', oprleft =3D> 'bpchar', oprright =3D> 'bpchar', oprresult =3D> 'bool', oprcom =3D> '<=3D(bpchar,bpchar)', oprnegate =3D> '<(bpchar,bpchar)', oprcode =3D> 'bpcharge', @@ -2330,7 +2330,7 @@ oprresult =3D> 'numeric', oprcode =3D> 'numeric_uplus' }, = # bytea operators -{ oid =3D> '1955', descr =3D> 'equal', +{ oid =3D> '1955', oid_symbol =3D> 'ByteaEqualOperator', descr =3D> 'equa= l', oprname =3D> '=3D', oprcanmerge =3D> 't', oprcanhash =3D> 't', oprleft = =3D> 'bytea', oprright =3D> 'bytea', oprresult =3D> 'bool', oprcom =3D> '=3D(bytea,by= tea)', oprnegate =3D> '<>(bytea,bytea)', oprcode =3D> 'byteaeq', oprrest =3D> = 'eqsel', @@ -2339,7 +2339,7 @@ oprname =3D> '<>', oprleft =3D> 'bytea', oprright =3D> 'bytea', oprresu= lt =3D> 'bool', oprcom =3D> '<>(bytea,bytea)', oprnegate =3D> '=3D(bytea,bytea)', oprcode =3D> 'byteane', oprrest =3D> 'neqsel', oprjoin =3D> 'neqjoinsel= ' }, -{ oid =3D> '1957', descr =3D> 'less than', +{ oid =3D> '1957', oid_symbol =3D> 'ByteaLessOperator', descr =3D> 'less = than', oprname =3D> '<', oprleft =3D> 'bytea', oprright =3D> 'bytea', oprresul= t =3D> 'bool', oprcom =3D> '>(bytea,bytea)', oprnegate =3D> '>=3D(bytea,bytea)', oprcode =3D> 'bytealt', oprrest =3D> 'scalarltsel', @@ -2354,7 +2354,7 @@ oprcom =3D> '<(bytea,bytea)', oprnegate =3D> '<=3D(bytea,bytea)', oprcode =3D> 'byteagt', oprrest =3D> 'scalargtsel', oprjoin =3D> 'scalargtjoinsel' }, -{ oid =3D> '1960', descr =3D> 'greater than or equal', +{ oid =3D> '1960', oid_symbol =3D> 'ByteaGreaterEqualOperator', descr =3D= > 'greater than or equal', oprname =3D> '>=3D', oprleft =3D> 'bytea', oprright =3D> 'bytea', oprre= sult =3D> 'bool', oprcom =3D> '<=3D(bytea,bytea)', oprnegate =3D> '<(bytea,bytea)', oprcode =3D> 'byteage', oprrest =3D> 'scalargesel', @@ -2416,7 +2416,7 @@ oprresult =3D> 'timestamp', oprcode =3D> 'timestamp_mi_interval' }, = # character-by-character (not collation order) comparison operators for c= haracter types -{ oid =3D> '2314', descr =3D> 'less than', +{ oid =3D> '2314', oid_symbol =3D> 'TextPatternLessOperator', descr =3D> = 'less than', oprname =3D> '~<~', oprleft =3D> 'text', oprright =3D> 'text', oprresul= t =3D> 'bool', oprcom =3D> '~>~(text,text)', oprnegate =3D> '~>=3D~(text,text)', oprcode =3D> 'text_pattern_lt', oprrest =3D> 'scalarltsel', @@ -2426,7 +2426,7 @@ oprcom =3D> '~>=3D~(text,text)', oprnegate =3D> '~>~(text,text)', oprcode =3D> 'text_pattern_le', oprrest =3D> 'scalarlesel', oprjoin =3D> 'scalarlejoinsel' }, -{ oid =3D> '2317', descr =3D> 'greater than or equal', +{ oid =3D> '2317', oid_symbol =3D> 'TextPatternGreaterEqualOperator', des= cr =3D> 'greater than or equal', oprname =3D> '~>=3D~', oprleft =3D> 'text', oprright =3D> 'text', oprre= sult =3D> 'bool', oprcom =3D> '~<=3D~(text,text)', oprnegate =3D> '~<~(text,text)', oprcode =3D> 'text_pattern_ge', oprrest =3D> 'scalargesel', @@ -2437,7 +2437,7 @@ oprcode =3D> 'text_pattern_gt', oprrest =3D> 'scalargtsel', oprjoin =3D> 'scalargtjoinsel' }, = -{ oid =3D> '2326', descr =3D> 'less than', +{ oid =3D> '2326', oid_symbol =3D> 'BpcharPatternLessOperator', descr =3D= > 'less than', oprname =3D> '~<~', oprleft =3D> 'bpchar', oprright =3D> 'bpchar', oprresult =3D> 'bool', oprcom =3D> '~>~(bpchar,bpchar)', oprnegate =3D> '~>=3D~(bpchar,bpchar)', oprcode =3D> 'bpchar_pattern_lt= ', @@ -2447,7 +2447,7 @@ oprresult =3D> 'bool', oprcom =3D> '~>=3D~(bpchar,bpchar)', oprnegate =3D> '~>~(bpchar,bpchar)', oprcode =3D> 'bpchar_pattern_le', oprrest =3D> 'scalarlesel', oprjoin =3D> 'scalarlejoinsel' }, -{ oid =3D> '2329', descr =3D> 'greater than or equal', +{ oid =3D> '2329', oid_symbol =3D> 'BpcharPatternGreaterEqualOperator', d= escr =3D> 'greater than or equal', oprname =3D> '~>=3D~', oprleft =3D> 'bpchar', oprright =3D> 'bpchar', oprresult =3D> 'bool', oprcom =3D> '~<=3D~(bpchar,bpchar)', oprnegate =3D> '~<~(bpchar,bpchar)', oprcode =3D> 'bpchar_pattern_ge', ------- =_aaaaaaaaaa0--