pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
No = operator for opfamily 426
3+ messages / 2 participants
[nested] [flat]

Commits from this thread:
  2019-11-20 9ff5b699ed  Sync patternsel_common's operator selection logic with pattern_prefix's.
  2019-11-20 2ddedcafca  Reduce match_pattern_prefix()'s dependencies on index opfamilies.
  2019-11-19 b3c265d7be bffe18e3e7  Fix corner-case failure in match_pattern_prefix().
* No = operator for opfamily 426
@ 2019-11-19 14:31 Manuel Rigger <rigger.manuel@gmail.com>
  2019-11-19 15:47 ` Re: No = operator for opfamily 426 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Manuel Rigger @ 2019-11-19 14:31 UTC (permalink / raw)
  To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Hi everyone,

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 = operator for opfamily 426

Unexpectedly, the index seems to create problems for the subsequent
query. When replacing the TEXT type by CHAR, the statements execute
successfully. Should it be possible to use this opclass for the TEXT
type?

Best,
Manuel





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: No = operator for opfamily 426
  2019-11-19 14:31 No = operator for opfamily 426 Manuel Rigger <rigger.manuel@gmail.com>
@ 2019-11-19 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
  2019-11-19 23:31   ` Re: No = operator for opfamily 426 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2019-11-19 15:47 UTC (permalink / raw)
  To: Manuel Rigger <rigger.manuel@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Manuel Rigger <rigger.manuel@gmail.com> 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 = operator for opfamily 426

Hm.  Right offhand, I'm wondering why we don't reject that index
specification.  I guess it's because we can use the index for
weird cases like

regression=# explain SELECT * FROM t0 WHERE t0.c0::bpchar = '';
                           QUERY PLAN                            
-----------------------------------------------------------------
 Bitmap Heap Scan on t0  (cost=4.21..14.35 rows=7 width=32)
   Recheck Cond: ((c0)::bpchar = ''::bpchar)
   ->  Bitmap Index Scan on i0  (cost=0.00..4.21 rows=7 width=0)
         Index Cond: ((c0)::bpchar = ''::bpchar)
(4 rows)

and even

regression=# explain SELECT * FROM t0 WHERE t0.c0::bpchar like '';
                           QUERY PLAN                            
-----------------------------------------------------------------
 Bitmap Heap Scan on t0  (cost=4.21..14.35 rows=7 width=32)
   Filter: ((c0)::bpchar ~~ ''::text)
   ->  Bitmap Index Scan on i0  (cost=0.00..4.21 rows=7 width=0)
         Index Cond: ((c0)::bpchar = ''::bpchar)
(4 rows)

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.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: No = operator for opfamily 426
  2019-11-19 14:31 No = operator for opfamily 426 Manuel Rigger <rigger.manuel@gmail.com>
  2019-11-19 15:47 ` Re: No = operator for opfamily 426 Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-11-19 23:31   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2019-11-19 23:31 UTC (permalink / raw)
  To: Manuel Rigger <rigger.manuel@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

I wrote:
> Manuel Rigger <rigger.manuel@gmail.com> 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 = operator for opfamily 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=# create table t (f1 text);
CREATE TABLE
regression=# create index on t using hash(f1);
CREATE INDEX
regression=# explain select * from t where f1 like 'foo';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=4.05..14.20 rows=7 width=32)
   Filter: (f1 ~~ 'foo'::text)
   ->  Bitmap Index Scan on t_f1_idx  (cost=0.00..4.05 rows=7 width=0)
         Index Cond: (f1 = '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

Attachments:

revise-pattern-match-indexqual-expansion.patch (text/x-diff, 14.6K) download 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 ">=".) - * Currently, only btree and spgist support the operators we need. - * - * Note: actually, in the Pattern_Prefix_Exact case, we only need "=" 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) should - * 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-legacy + * "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 = exprType(leftop); + switch (ldatatype) { - case TEXT_BTREE_FAM_OID: - if (!(pstatus == Pattern_Prefix_Exact || - lc_collate_is_c(indexcollation))) - return NIL; + case TEXTOID: + if (opfamily == TEXT_PATTERN_BTREE_FAM_OID || + opfamily == TEXT_SPGIST_FAM_OID) + { + eqopr = TextEqualOperator; + ltopr = TextPatternLessOperator; + geopr = TextPatternGreaterEqualOperator; + collation_aware = false; + } + else + { + eqopr = TextEqualOperator; + ltopr = TextLessOperator; + geopr = TextGreaterEqualOperator; + collation_aware = true; + } rdatatype = 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 = NameEqualTextOperator; + ltopr = NameLessTextOperator; + geopr = NameGreaterEqualTextOperator; + collation_aware = true; rdatatype = TEXTOID; break; - - case BPCHAR_BTREE_FAM_OID: - if (!(pstatus == Pattern_Prefix_Exact || - lc_collate_is_c(indexcollation))) - return NIL; - rdatatype = BPCHAROID; - break; - - case BPCHAR_PATTERN_BTREE_FAM_OID: + case BPCHAROID: + if (opfamily == BPCHAR_PATTERN_BTREE_FAM_OID) + { + eqopr = BpcharEqualOperator; + ltopr = BpcharPatternLessOperator; + geopr = BpcharPatternGreaterEqualOperator; + collation_aware = false; + } + else + { + eqopr = BpcharEqualOperator; + ltopr = BpcharLessOperator; + geopr = BpcharGreaterEqualOperator; + collation_aware = true; + } rdatatype = BPCHAROID; break; - - case BYTEA_BTREE_FAM_OID: + case BYTEAOID: + eqopr = ByteaEqualOperator; + ltopr = ByteaLessOperator; + geopr = ByteaGreaterEqualOperator; + collation_aware = false; rdatatype = 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 = exprType(leftop); + /* + * If necessary, verify that the index's collation behavior is compatible. + * For an exact-match case, we don't have to be picky. Otherwise, insist + * that the index collation be "C". 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. + */ + if (collation_aware) + { + if (!(pstatus == Pattern_Prefix_Exact || + lc_collate_is_c(indexcollation))) + return NIL; + } /* * If necessary, coerce the prefix constant to the right type. The given @@ -358,16 +389,17 @@ match_pattern_prefix(Node *leftop, /* * If we found an exact-match pattern, generate an "=" 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 == Pattern_Prefix_Exact) { - oproid = get_opfamily_member(opfamily, ldatatype, rdatatype, - BTEqualStrategyNumber); - if (oproid == InvalidOid) + if (!op_in_opfamily(eqopr, opfamily)) return NIL; - expr = make_opclause(oproid, BOOLOID, false, + expr = make_opclause(eqopr, BOOLOID, false, (Expr *) leftop, (Expr *) prefix, InvalidOid, indexcollation); result = list_make1(expr); @@ -379,11 +411,9 @@ match_pattern_prefix(Node *leftop, * * We can always say "x >= prefix". */ - oproid = get_opfamily_member(opfamily, ldatatype, rdatatype, - BTGreaterEqualStrategyNumber); - if (oproid == InvalidOid) + if (!op_in_opfamily(geopr, opfamily)) return NIL; - expr = make_opclause(oproid, BOOLOID, false, + expr = make_opclause(geopr, BOOLOID, false, (Expr *) leftop, (Expr *) prefix, InvalidOid, indexcollation); result = list_make1(expr); @@ -396,15 +426,13 @@ match_pattern_prefix(Node *leftop, * using a C-locale index collation. *------- */ - oproid = get_opfamily_member(opfamily, ldatatype, rdatatype, - BTLessStrategyNumber); - if (oproid == InvalidOid) + if (!op_in_opfamily(ltopr, opfamily)) return result; - fmgr_info(get_opcode(oproid), &ltproc); + fmgr_info(get_opcode(ltopr), &ltproc); greaterstr = make_greater_string(prefix, &ltproc, indexcollation); if (greaterstr) { - expr = make_opclause(oproid, BOOLOID, false, + expr = make_opclause(ltopr, BOOLOID, false, (Expr *) leftop, (Expr *) greaterstr, InvalidOid, indexcollation); result = 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 => 'starts_with', oprrest => 'prefixsel', oprjoin => 'prefixjoinsel' }, -{ oid => '254', descr => 'equal', +{ oid => '254', oid_symbol => 'NameEqualTextOperator', descr => 'equal', oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'name', oprright => 'text', oprresult => 'bool', oprcom => '=(text,name)', oprnegate => '<>(name,text)', oprcode => 'nameeqtext', oprrest => 'eqsel', oprjoin => 'eqjoinsel' }, -{ oid => '255', descr => 'less than', +{ oid => '255', oid_symbol => 'NameLessTextOperator', descr => 'less than', oprname => '<', oprleft => 'name', oprright => 'text', oprresult => 'bool', oprcom => '>(text,name)', oprnegate => '>=(name,text)', oprcode => 'namelttext', oprrest => 'scalarltsel', @@ -122,7 +122,7 @@ oprcom => '>=(text,name)', oprnegate => '>(name,text)', oprcode => 'nameletext', oprrest => 'scalarlesel', oprjoin => 'scalarlejoinsel' }, -{ oid => '257', descr => 'greater than or equal', +{ oid => '257', oid_symbol => 'NameGreaterEqualTextOperator', descr => 'greater than or equal', oprname => '>=', oprleft => 'name', oprright => 'text', oprresult => 'bool', oprcom => '<=(text,name)', oprnegate => '<(name,text)', oprcode => 'namegetext', oprrest => 'scalargesel', @@ -789,7 +789,7 @@ oprname => '>=', oprleft => 'name', oprright => 'name', oprresult => 'bool', oprcom => '<=(name,name)', oprnegate => '<(name,name)', oprcode => 'namege', oprrest => 'scalargesel', oprjoin => 'scalargejoinsel' }, -{ oid => '664', descr => 'less than', +{ oid => '664', oid_symbol => 'TextLessOperator', descr => 'less than', oprname => '<', oprleft => 'text', oprright => 'text', oprresult => 'bool', oprcom => '>(text,text)', oprnegate => '>=(text,text)', oprcode => 'text_lt', oprrest => 'scalarltsel', oprjoin => 'scalarltjoinsel' }, @@ -801,7 +801,7 @@ oprname => '>', oprleft => 'text', oprright => 'text', oprresult => 'bool', oprcom => '<(text,text)', oprnegate => '<=(text,text)', oprcode => 'text_gt', oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' }, -{ oid => '667', descr => 'greater than or equal', +{ oid => '667', oid_symbol => 'TextGreaterEqualOperator', descr => 'greater than or equal', oprname => '>=', oprleft => 'text', oprright => 'text', oprresult => 'bool', oprcom => '<=(text,text)', oprnegate => '<(text,text)', oprcode => 'text_ge', oprrest => 'scalargesel', oprjoin => 'scalargejoinsel' }, @@ -1160,7 +1160,7 @@ oprname => '@@', oprkind => 'l', oprleft => '0', oprright => 'polygon', oprresult => 'point', oprcode => 'poly_center' }, -{ oid => '1054', descr => 'equal', +{ oid => '1054', oid_symbol => 'BpcharEqualOperator', descr => 'equal', oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'bpchar', oprright => 'bpchar', oprresult => 'bool', oprcom => '=(bpchar,bpchar)', oprnegate => '<>(bpchar,bpchar)', oprcode => 'bpchareq', oprrest => 'eqsel', @@ -1180,7 +1180,7 @@ oprresult => 'bool', oprcom => '<>(bpchar,bpchar)', oprnegate => '=(bpchar,bpchar)', oprcode => 'bpcharne', oprrest => 'neqsel', oprjoin => 'neqjoinsel' }, -{ oid => '1058', descr => 'less than', +{ oid => '1058', oid_symbol => 'BpcharLessOperator', descr => 'less than', oprname => '<', oprleft => 'bpchar', oprright => 'bpchar', oprresult => 'bool', oprcom => '>(bpchar,bpchar)', oprnegate => '>=(bpchar,bpchar)', oprcode => 'bpcharlt', @@ -1195,7 +1195,7 @@ oprresult => 'bool', oprcom => '<(bpchar,bpchar)', oprnegate => '<=(bpchar,bpchar)', oprcode => 'bpchargt', oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' }, -{ oid => '1061', descr => 'greater than or equal', +{ oid => '1061', oid_symbol => 'BpcharGreaterEqualOperator', descr => 'greater than or equal', oprname => '>=', oprleft => 'bpchar', oprright => 'bpchar', oprresult => 'bool', oprcom => '<=(bpchar,bpchar)', oprnegate => '<(bpchar,bpchar)', oprcode => 'bpcharge', @@ -2330,7 +2330,7 @@ oprresult => 'numeric', oprcode => 'numeric_uplus' }, # bytea operators -{ oid => '1955', descr => 'equal', +{ oid => '1955', oid_symbol => 'ByteaEqualOperator', descr => 'equal', oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool', oprcom => '=(bytea,bytea)', oprnegate => '<>(bytea,bytea)', oprcode => 'byteaeq', oprrest => 'eqsel', @@ -2339,7 +2339,7 @@ oprname => '<>', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool', oprcom => '<>(bytea,bytea)', oprnegate => '=(bytea,bytea)', oprcode => 'byteane', oprrest => 'neqsel', oprjoin => 'neqjoinsel' }, -{ oid => '1957', descr => 'less than', +{ oid => '1957', oid_symbol => 'ByteaLessOperator', descr => 'less than', oprname => '<', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool', oprcom => '>(bytea,bytea)', oprnegate => '>=(bytea,bytea)', oprcode => 'bytealt', oprrest => 'scalarltsel', @@ -2354,7 +2354,7 @@ oprcom => '<(bytea,bytea)', oprnegate => '<=(bytea,bytea)', oprcode => 'byteagt', oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' }, -{ oid => '1960', descr => 'greater than or equal', +{ oid => '1960', oid_symbol => 'ByteaGreaterEqualOperator', descr => 'greater than or equal', oprname => '>=', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool', oprcom => '<=(bytea,bytea)', oprnegate => '<(bytea,bytea)', oprcode => 'byteage', oprrest => 'scalargesel', @@ -2416,7 +2416,7 @@ oprresult => 'timestamp', oprcode => 'timestamp_mi_interval' }, # character-by-character (not collation order) comparison operators for character types -{ oid => '2314', descr => 'less than', +{ oid => '2314', oid_symbol => 'TextPatternLessOperator', descr => 'less than', oprname => '~<~', oprleft => 'text', oprright => 'text', oprresult => 'bool', oprcom => '~>~(text,text)', oprnegate => '~>=~(text,text)', oprcode => 'text_pattern_lt', oprrest => 'scalarltsel', @@ -2426,7 +2426,7 @@ oprcom => '~>=~(text,text)', oprnegate => '~>~(text,text)', oprcode => 'text_pattern_le', oprrest => 'scalarlesel', oprjoin => 'scalarlejoinsel' }, -{ oid => '2317', descr => 'greater than or equal', +{ oid => '2317', oid_symbol => 'TextPatternGreaterEqualOperator', descr => 'greater than or equal', oprname => '~>=~', oprleft => 'text', oprright => 'text', oprresult => 'bool', oprcom => '~<=~(text,text)', oprnegate => '~<~(text,text)', oprcode => 'text_pattern_ge', oprrest => 'scalargesel', @@ -2437,7 +2437,7 @@ oprcode => 'text_pattern_gt', oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' }, -{ oid => '2326', descr => 'less than', +{ oid => '2326', oid_symbol => 'BpcharPatternLessOperator', descr => 'less than', oprname => '~<~', oprleft => 'bpchar', oprright => 'bpchar', oprresult => 'bool', oprcom => '~>~(bpchar,bpchar)', oprnegate => '~>=~(bpchar,bpchar)', oprcode => 'bpchar_pattern_lt', @@ -2447,7 +2447,7 @@ oprresult => 'bool', oprcom => '~>=~(bpchar,bpchar)', oprnegate => '~>~(bpchar,bpchar)', oprcode => 'bpchar_pattern_le', oprrest => 'scalarlesel', oprjoin => 'scalarlejoinsel' }, -{ oid => '2329', descr => 'greater than or equal', +{ oid => '2329', oid_symbol => 'BpcharPatternGreaterEqualOperator', descr => 'greater than or equal', oprname => '~>=~', oprleft => 'bpchar', oprright => 'bpchar', oprresult => 'bool', oprcom => '~<=~(bpchar,bpchar)', oprnegate => '~<~(bpchar,bpchar)', oprcode => 'bpchar_pattern_ge',
^ permalink raw reply [nested|flat] 3+ messages in thread


end of thread, other threads:[~2019-11-19 23:31 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-19 14:31 No = operator for opfamily 426 Manuel Rigger <rigger.manuel@gmail.com>
2019-11-19 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-19 23:31   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox