Обсуждение: bytea operator bugs (was Re: [GENERAL] BYTEA, indexes and "like")
Alvar Freude wrote: > it seems, that a BYTEA fiels doesn't support indexes in WHERE-Statement > with a "like" condition: I started to look at this issue and ran into two possibly unrelated bugs. The first was an assert failure in patternsel(). It was looking for strictly TEXT as the right-hand const. I guess when I originally did the bytea comparison operators last year I didn't have assert checking on :( In any case attached is a small patch for that one. The second one I need help with. The basic problem is that whenever an index is used on bytea, and no matching records are found, I get "ERROR: Index bombytea_idx1 is not a btree". E.g. parts=# explain select * from bombytea where parent_part = '02'; QUERY PLAN ---------------------------------------------------------------------------------- Index Scan using bombytea_idx1 on bombytea (cost=0.00..147.83 rows=37 width=34) Index Cond: (parent_part = '02'::bytea) (2 rows) parts=# explain analyze select * from bombytea where parent_part = '02'; ERROR: Index bombytea_idx1 is not a btree In fact, a little further testing shows any bytea index use now fails, so maybe this is new in development sources: parts=# explain analyze select * from bombytea where parent_part >= '02-05000-0' limit 1; QUERY PLAN ------------------------------------------------------------------------ Limit (cost=0.00..0.02 rows=1 width=34) (actual time=0.03..0.04 rows=1 loops=1) -> Seq Scan on bombytea (cost=0.00..4677.85 rows=213026 width=34) (actual time=0.03..0.03 rows=2 loops=1) Filter: (parent_part >= '02-05000-0'::bytea) Total runtime: 0.17 msec (4 rows) parts=# select * from bombytea where parent_part >= '02-05000-0' limit 1; parent_part | child_part | child_part_qty --------------+------------+---------------- FM04-13100-1 | NULL | 0 (1 row) parts=# explain select * from bombytea where parent_part = 'FM04-13100-1' limit 1; QUERY PLAN ---------------------------------------------------------------------------------------- Limit (cost=0.00..4.03 rows=1 width=34) -> Index Scan using bombytea_idx1 on bombytea (cost=0.00..147.83 rows=37 width=34) Index Cond: (parent_part = 'FM04-13100-1'::bytea) (3 rows) parts=# select * from bombytea where parent_part = 'FM04-13100-1' limit 1; ERROR: Index bombytea_idx1 is not a btree I've isolated this down to _bt_getroot() to the following line (about line 125 in nbtpage.c): if (!(metaopaque->btpo_flags & BTP_META) || metad->btm_magic != BTREE_MAGIC) elog(ERROR, "Index %s is not a btree", RelationGetRelationName(rel)); and more specifically to "!(metaopaque->btpo_flags & BTP_META)". But I haven't been able to see any difference between the bytea case which fails, and text or varchar which do not. Any ideas what else I should be looking at? Thanks, Joe Index: src/backend/utils/adt/selfuncs.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/selfuncs.c,v retrieving revision 1.112 diff -c -r1.112 selfuncs.c *** src/backend/utils/adt/selfuncs.c 20 Jun 2002 20:29:38 -0000 1.112 --- src/backend/utils/adt/selfuncs.c 18 Aug 2002 18:57:04 -0000 *************** *** 853,861 **** if (((Const *) other)->constisnull) return 0.0; constval = ((Const *) other)->constvalue; ! /* the right-hand const is type text for all supported operators */ ! Assert(((Const *) other)->consttype == TEXTOID); ! patt = DatumGetCString(DirectFunctionCall1(textout, constval)); /* divide pattern into fixed prefix and remainder */ pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); --- 853,865 ---- if (((Const *) other)->constisnull) return 0.0; constval = ((Const *) other)->constvalue; ! /* the right-hand const is type text or bytea for all supported operators */ ! Assert(((Const *) other)->consttype == TEXTOID || ! ((Const *) other)->consttype == BYTEAOID); ! if (((Const *) other)->consttype == TEXTOID) ! patt = DatumGetCString(DirectFunctionCall1(textout, constval)); ! else ! patt = DatumGetCString(DirectFunctionCall1(byteaout, constval)); /* divide pattern into fixed prefix and remainder */ pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest);
Joe Conway <mail@joeconway.com> writes: > The first was an assert failure in patternsel(). It was looking for > strictly TEXT as the right-hand const. I guess when I originally did the > bytea comparison operators last year I didn't have assert checking on :( > In any case attached is a small patch for that one. This is not gonna fly: the reason why that Assert is there is that the rest of the pattern-analysis code assumes it can work with null-terminated strings. If you want a one-line patch then the patch is to change the right-hand argument of bytealike to TEXT. A "real" patch would probably require changing all the patternsel routines to use counted strings, which seems like it will add major uglification. > I've isolated this down to _bt_getroot() to the following line (about > line 125 in nbtpage.c): > if (!(metaopaque->btpo_flags & BTP_META) || > metad->btm_magic != BTREE_MAGIC) > elog(ERROR, "Index %s is not a btree", > RelationGetRelationName(rel)); > and more specifically to "!(metaopaque->btpo_flags & BTP_META)". > But I haven't been able to see any difference between the bytea case > which fails, and text or varchar which do not. I'm betting on a memory-clobber kind of problem --- I think something in the bytea-related code is stomping on the disk buffer that holds the btree metapage. Are you testing with --enable-cassert now? (That turns on MEMORY_CHECKING which might be helpful...) regards, tom lane
Tom Lane wrote: > I'm betting on a memory-clobber kind of problem --- I think something in > the bytea-related code is stomping on the disk buffer that holds the > btree metapage. Are you testing with --enable-cassert now? (That turns > on MEMORY_CHECKING which might be helpful...) My standard configure line these days is: ./configure --enable-integer-datetimes --enable-locale --enable-debug --enable-cassert --enable-multibyte --enable-syslog --enable-nls --enable-depend and: parts=# show debug_assertions; debug_assertions ------------------ on (1 row) And I'm definitely seeing the asserts. I'll work on a proper fix for the first item and continue to investigate the second. Thanks, Joe
Tom Lane wrote: > This is not gonna fly: the reason why that Assert is there is that > the rest of the pattern-analysis code assumes it can work with > null-terminated strings. If you want a one-line patch then the patch > is to change the right-hand argument of bytealike to TEXT. A "real" > patch would probably require changing all the patternsel routines to > use counted strings, which seems like it will add major uglification. OK. I'll look at both options and make another diff -c proposal ;-) Once that's resolved I'll go back to original issue Alvar raised. BTW, you were right-on on the other issue. I started with a fresh sync up from cvs, then configure, make all, make install, initdb. Now the problem is gone. As always, thanks for your help and guidance. Joe
Joe Conway <mail@joeconway.com> writes: > OK. I'll look at both options and make another diff -c proposal ;-) Once > that's resolved I'll go back to original issue Alvar raised. Okay. When you get back to the original issue, the gold is hidden in src/backend/optimizer/path/indxpath.c; see the "special indexable operators" stuff near the bottom of that file. (It's a bit of a crock that this code is hardwired there, and not somehow accessed through a system catalog, but it's what we've got at the moment.) regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > >>OK. I'll look at both options and make another diff -c proposal ;-) Once >>that's resolved I'll go back to original issue Alvar raised. > > Okay. When you get back to the original issue, the gold is hidden in > src/backend/optimizer/path/indxpath.c; see the "special indexable > operators" stuff near the bottom of that file. (It's a bit of a crock > that this code is hardwired there, and not somehow accessed through a > system catalog, but it's what we've got at the moment.) Thanks. Back on the pattern selectivity issue. With some more study I can clearly see what you were referring to. Dragging string length through the maze of function calls that would need it would be a mess. In the longer term (i.e. not for 7.3) it might make sense to create a set of pattern selectivity functions, just for bytea, that are careful to avoid the null-terminated string assumption. But, for now, I'm leaning toward restricting the right-hand argument of bytealike to TEXT, as you suggested. Joe
Joe Conway wrote: > BTW, you were right-on on the other issue. I started with a fresh sync > up from cvs, then configure, make all, make install, initdb. Now the > problem is gone. Hmmm -- I might have to take that back. Take a look at the following session: parts=# SELECT * from bombytea where parent_part > '02-' and parent_part < '02-1'; parent_part | child_part | child_part_qty -------------+--------------+---------------- 02-00010-0 | NULL | 0 02-00015-0 | NULL | 0 02-00015-1 | ... 02-06360-00 | 46-01-12700 | 0 02-06360-00 | 02-03360-0 | 1 (492 rows) parts=# SELECT * from bombytea where parent_part like '02-%'; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. parts=# \q [root@jec-linux pgsql]# psql -U postgres parts Welcome to psql 7.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit parts=# SELECT * from bombytea where parent_part > '02-' and parent_part < '02-1'; ERROR: Index bombytea_idx1 is not a btree parts=# \q [root@jec-linux pgsql]# /etc/init.d/postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] [root@jec-linux pgsql]# psql -U postgres parts Welcome to psql 7.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit parts=# SELECT * from bombytea where parent_part > '02-' and parent_part < '02-1'; ERROR: Index bombytea_idx1 is not a btree parts=# drop index bombytea_idx1; DROP INDEX parts=# create index bombytea_idx1 on bombytea (parent_part); CREATE INDEX parts=# SELECT * from bombytea where parent_part > '02-' and parent_part < '02-1'; parent_part | child_part | child_part_qty -------------+--------------+---------------- 02-00010-0 | NULL | 0 02-00015-0 | ... 02-06360-00 | 46-01-12700 | 0 02-06360-00 | 02-03360-0 | 1 (492 rows) So it appears that "SELECT * from bombytea where parent_part like '02-%'; server closed the connection unexpectedly" somehow physically corrupts the index!? This is starting from `make clean` with an initdb just prior. Any ideas? Joe
Joe Conway <mail@joeconway.com> writes: > parts=# SELECT * from bombytea where parent_part like '02-%'; > server closed the connection unexpectedly You should stop right here and investigate the reason for the crash. > So it appears that "SELECT * from bombytea where parent_part like > '02-%'; server closed the connection unexpectedly" somehow physically > corrupts the index! Hm, I'd have expected WAL recovery to fix that. But the easiest avenue of attack is to isolate the reason for the crash. Please post a test case if you want help. regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > >>parts=# SELECT * from bombytea where parent_part like '02-%'; >>server closed the connection unexpectedly > > You should stop right here and investigate the reason for the crash. It's the same reason as before -- i.e. the TEXTOID assertion in patternsel(). I just didn't expect to see physical index corruption as a result. >>So it appears that "SELECT * from bombytea where parent_part like >>'02-%'; server closed the connection unexpectedly" somehow physically >>corrupts the index! > > Hm, I'd have expected WAL recovery to fix that. But the easiest avenue > of attack is to isolate the reason for the crash. Please post a test > case if you want help. OK. I'll be back at this again this morning on a different development machine. I'll see if I can get a reliable test case and post it. Joe
Joe Conway <mail@joeconway.com> writes: > Tom Lane wrote: >> You should stop right here and investigate the reason for the crash. > It's the same reason as before -- i.e. the TEXTOID assertion in > patternsel(). I just didn't expect to see physical index corruption as a > result. Oh really? Yeah, I'm surprised too (and not happy). Let's definitely see that test case... regards, tom lane
Tom Lane wrote: > Oh really? Yeah, I'm surprised too (and not happy). Let's definitely > see that test case... Here is a test case. Prior to running the following two sessions, I did the following: /etc/init.d/postgresql stop rm -rf pgsql cvsup to resync with cvs cvs co pgsql cd pgsql ./configure --enable-integer-datetimes --enable-locale --enable-debug --enable-cassert --enable-multibyte --enable-syslog --enable-nls --enable-depend make all make install initdb /etc/init.d/postgresql start Note this this is without any attempt to fix the TEXTOID assertion in patternsel() -- this is unaltered cvs tip. The two sequntial psql sessions are below. Thanks, Joe First session: ============== [root@jec-linux-1 pgsql]# psql -U postgres test Welcome to psql 7.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# ------------------------------------------------------ test=# -- Session 1: test=# ------------------------------------------------------ test=# drop table foobytea; DROP TABLE test=# create table foobytea(f1 bytea); CREATE TABLE test=# insert into foobytea values('crash'); INSERT 698676 1 test=# create index foobytea_idx on foobytea(f1); CREATE INDEX test=# set enable_seqscan = off; SET test=# select f1 from foobytea where f1 = 'crash'; f1 ------- crash (1 row) test=# explain select f1 from foobytea where f1 like 'cr%'; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. test=# \q Immediately followed by second session: ======================================= [root@jec-linux-1 pgsql]# psql -U postgres test Welcome to psql 7.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# ------------------------------------------------------ test=# -- Session 2: test=# ------------------------------------------------------ test=# set enable_seqscan = off; SET test=# explain select f1 from foobytea where f1 = 'crash'; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using foobytea_idx on foobytea (cost=0.00..4.68 rows=1 width=32) Index Cond: (f1 = 'crash'::bytea) (2 rows) test=# select f1 from foobytea where f1 = 'crash'; ERROR: Index foobytea_idx is not a btree test=# drop index foobytea_idx; DROP INDEX test=# create index foobytea_idx on foobytea(f1); CREATE INDEX test=# explain select f1 from foobytea where f1 = 'crash'; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using foobytea_idx on foobytea (cost=0.00..4.68 rows=1 width=32) Index Cond: (f1 = 'crash'::bytea) (2 rows) test=# select f1 from foobytea where f1 = 'crash'; f1 ------- crash (1 row) test=#
Joe Conway wrote: > Back on the pattern selectivity issue. With some more study I can > clearly see what you were referring to. Dragging string length through > the maze of function calls that would need it would be a mess. > > In the longer term (i.e. not for 7.3) it might make sense to create a > set of pattern selectivity functions, just for bytea, that are careful > to avoid the null-terminated string assumption. But, for now, I'm > leaning toward restricting the right-hand argument of bytealike to TEXT, > as you suggested. As suggested by Tom, this patch restricts the right-hand argument of bytealike to TEXT. This leaves like_escape_bytea() without anything to do, but I left it in place in anticipation of the eventual bytea pattern selectivity functions. If there is agreement that this would be the best long term solution, I'll take it as a TODO for 7.4. I'll look around the docs to see if there is someplace where a note wrt this is appropriate. If there are no objections, please apply. Thanks, Joe Index: src/backend/utils/adt/like.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v retrieving revision 1.49 diff -c -r1.49 like.c *** src/backend/utils/adt/like.c 20 Jun 2002 20:29:37 -0000 1.49 --- src/backend/utils/adt/like.c 19 Aug 2002 17:06:10 -0000 *************** *** 264,270 **** bytealike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! bytea *pat = PG_GETARG_BYTEA_P(1); bool result; unsigned char *s, *p; --- 264,270 ---- bytealike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! text *pat = PG_GETARG_TEXT_P(1); bool result; unsigned char *s, *p; *************** *** 285,291 **** byteanlike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! bytea *pat = PG_GETARG_BYTEA_P(1); bool result; unsigned char *s, *p; --- 285,291 ---- byteanlike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! text *pat = PG_GETARG_TEXT_P(1); bool result; unsigned char *s, *p; Index: src/include/catalog/pg_operator.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v retrieving revision 1.106 diff -c -r1.106 pg_operator.h *** src/include/catalog/pg_operator.h 24 Jul 2002 19:11:12 -0000 1.106 --- src/include/catalog/pg_operator.h 19 Aug 2002 17:43:31 -0000 *************** *** 827,835 **** DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); #define OID_BYTEA_LIKE_OP 2016 ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); /* timestamp operators */ --- 827,835 ---- DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); #define OID_BYTEA_LIKE_OP 2016 ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); /* timestamp operators */ Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v retrieving revision 1.256 diff -c -r1.256 pg_proc.h *** src/include/catalog/pg_proc.h 17 Aug 2002 13:04:15 -0000 1.256 --- src/include/catalog/pg_proc.h 19 Aug 2002 17:06:10 -0000 *************** *** 2766,2778 **** DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); DESCR("adjust time with time zone precision"); ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); DESCR("does not match LIKE expression"); ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); DESCR("does not match LIKE expression"); DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); DESCR("convert match pattern to use backslash escapes"); --- 2766,2778 ---- DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); DESCR("adjust time with time zone precision"); ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); DESCR("does not match LIKE expression"); ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); DESCR("does not match LIKE expression"); DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); DESCR("convert match pattern to use backslash escapes");
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Joe Conway wrote: > Joe Conway wrote: > > Back on the pattern selectivity issue. With some more study I can > > clearly see what you were referring to. Dragging string length through > > the maze of function calls that would need it would be a mess. > > > > In the longer term (i.e. not for 7.3) it might make sense to create a > > set of pattern selectivity functions, just for bytea, that are careful > > to avoid the null-terminated string assumption. But, for now, I'm > > leaning toward restricting the right-hand argument of bytealike to TEXT, > > as you suggested. > > As suggested by Tom, this patch restricts the right-hand argument of > bytealike to TEXT. > > This leaves like_escape_bytea() without anything to do, but I left it in > place in anticipation of the eventual bytea pattern selectivity > functions. If there is agreement that this would be the best long term > solution, I'll take it as a TODO for 7.4. > > I'll look around the docs to see if there is someplace where a note wrt > this is appropriate. > > If there are no objections, please apply. > > Thanks, > > Joe > Index: src/backend/utils/adt/like.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v > retrieving revision 1.49 > diff -c -r1.49 like.c > *** src/backend/utils/adt/like.c 20 Jun 2002 20:29:37 -0000 1.49 > --- src/backend/utils/adt/like.c 19 Aug 2002 17:06:10 -0000 > *************** > *** 264,270 **** > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > --- 264,270 ---- > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > *************** > *** 285,291 **** > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > --- 285,291 ---- > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > Index: src/include/catalog/pg_operator.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v > retrieving revision 1.106 > diff -c -r1.106 pg_operator.h > *** src/include/catalog/pg_operator.h 24 Jul 2002 19:11:12 -0000 1.106 > --- src/include/catalog/pg_operator.h 19 Aug 2002 17:43:31 -0000 > *************** > *** 827,835 **** > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > --- 827,835 ---- > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > Index: src/include/catalog/pg_proc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v > retrieving revision 1.256 > diff -c -r1.256 pg_proc.h > *** src/include/catalog/pg_proc.h 17 Aug 2002 13:04:15 -0000 1.256 > --- src/include/catalog/pg_proc.h 19 Aug 2002 17:06:10 -0000 > *************** > *** 2766,2778 **** > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > --- 2766,2778 ---- > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. Added to TODO: * Allow bytea to handle LIKE with non-TEXT patterns --------------------------------------------------------------------------- Joe Conway wrote: > Joe Conway wrote: > > Back on the pattern selectivity issue. With some more study I can > > clearly see what you were referring to. Dragging string length through > > the maze of function calls that would need it would be a mess. > > > > In the longer term (i.e. not for 7.3) it might make sense to create a > > set of pattern selectivity functions, just for bytea, that are careful > > to avoid the null-terminated string assumption. But, for now, I'm > > leaning toward restricting the right-hand argument of bytealike to TEXT, > > as you suggested. > > As suggested by Tom, this patch restricts the right-hand argument of > bytealike to TEXT. > > This leaves like_escape_bytea() without anything to do, but I left it in > place in anticipation of the eventual bytea pattern selectivity > functions. If there is agreement that this would be the best long term > solution, I'll take it as a TODO for 7.4. > > I'll look around the docs to see if there is someplace where a note wrt > this is appropriate. > > If there are no objections, please apply. > > Thanks, > > Joe > Index: src/backend/utils/adt/like.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v > retrieving revision 1.49 > diff -c -r1.49 like.c > *** src/backend/utils/adt/like.c 20 Jun 2002 20:29:37 -0000 1.49 > --- src/backend/utils/adt/like.c 19 Aug 2002 17:06:10 -0000 > *************** > *** 264,270 **** > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > --- 264,270 ---- > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > *************** > *** 285,291 **** > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > --- 285,291 ---- > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > Index: src/include/catalog/pg_operator.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v > retrieving revision 1.106 > diff -c -r1.106 pg_operator.h > *** src/include/catalog/pg_operator.h 24 Jul 2002 19:11:12 -0000 1.106 > --- src/include/catalog/pg_operator.h 19 Aug 2002 17:43:31 -0000 > *************** > *** 827,835 **** > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > --- 827,835 ---- > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > Index: src/include/catalog/pg_proc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v > retrieving revision 1.256 > diff -c -r1.256 pg_proc.h > *** src/include/catalog/pg_proc.h 17 Aug 2002 13:04:15 -0000 1.256 > --- src/include/catalog/pg_proc.h 19 Aug 2002 17:06:10 -0000 > *************** > *** 2766,2778 **** > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > --- 2766,2778 ---- > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >>OK. I'll look at both options and make another diff -c proposal ;-) Once >>that's resolved I'll go back to original issue Alvar raised. > > Okay. When you get back to the original issue, the gold is hidden in > src/backend/optimizer/path/indxpath.c; see the "special indexable > operators" stuff near the bottom of that file. (It's a bit of a crock > that this code is hardwired there, and not somehow accessed through a > system catalog, but it's what we've got at the moment.) The attached patch re-enables a bytea right hand argument (as compared to a text right hand argument), and enables index usage, for bytea LIKE -- e.g.: parts=# explain select * from bombytea where parent_part like '05-05%'; QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using bombytea_idx1 on bombytea (cost=0.00..3479.67 rows=1118 width=34) Index Cond: ((parent_part >= '05-05'::bytea) AND (parent_part < '05-06'::bytea)) Filter: (parent_part ~~ '05-05%'::bytea) (3 rows) Passes all regression tests, and as far as I can tell does not break or change the behavior of anything else. Please review and apply if there are no objections (I'd like to see this applied for 7.3, before the freeze, if possible, but I'll certainly understand if I'm told there's not enough time left). Thanks, Joe Index: src/backend/optimizer/path/indxpath.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/path/indxpath.c,v retrieving revision 1.120 diff -c -r1.120 indxpath.c *** src/backend/optimizer/path/indxpath.c 13 Jul 2002 19:20:34 -0000 1.120 --- src/backend/optimizer/path/indxpath.c 1 Sep 2002 22:19:16 -0000 *************** *** 97,103 **** static bool match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left); static List *prefix_quals(Var *leftop, Oid expr_op, ! char *prefix, Pattern_Prefix_Status pstatus); static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); static Oid find_operator(const char *opname, Oid datatype); static Datum string_to_datum(const char *str, Oid datatype); --- 97,103 ---- static bool match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left); static List *prefix_quals(Var *leftop, Oid expr_op, ! Const *prefix, Pattern_Prefix_Status pstatus); static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); static Oid find_operator(const char *opname, Oid datatype); static Datum string_to_datum(const char *str, Oid datatype); *************** *** 1675,1684 **** Var *leftop, *rightop; Oid expr_op; ! Datum constvalue; ! char *patt; ! char *prefix; ! char *rest; /* * Currently, all known special operators require the indexkey on the --- 1675,1683 ---- Var *leftop, *rightop; Oid expr_op; ! Const *patt = NULL; ! Const *prefix = NULL; ! Const *rest = NULL; /* * Currently, all known special operators require the indexkey on the *************** *** 1697,1703 **** if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull) return false; ! constvalue = ((Const *) rightop)->constvalue; switch (expr_op) { --- 1696,1702 ---- if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull) return false; ! patt = (Const *) rightop; switch (expr_op) { *************** *** 1705,1772 **** case OID_BPCHAR_LIKE_OP: case OID_VARCHAR_LIKE_OP: case OID_NAME_LIKE_OP: if (locale_is_like_safe()) - { - /* the right-hand const is type text for all of these */ - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, &prefix, &rest) != Pattern_Prefix_None; ! if (prefix) ! pfree(prefix); ! pfree(patt); ! } break; case OID_TEXT_ICLIKE_OP: case OID_BPCHAR_ICLIKE_OP: case OID_VARCHAR_ICLIKE_OP: case OID_NAME_ICLIKE_OP: if (locale_is_like_safe()) - { - /* the right-hand const is type text for all of these */ - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, &prefix, &rest) != Pattern_Prefix_None; - if (prefix) - pfree(prefix); - pfree(patt); - } break; case OID_TEXT_REGEXEQ_OP: case OID_BPCHAR_REGEXEQ_OP: case OID_VARCHAR_REGEXEQ_OP: case OID_NAME_REGEXEQ_OP: if (locale_is_like_safe()) - { - /* the right-hand const is type text for all of these */ - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, &prefix, &rest) != Pattern_Prefix_None; - if (prefix) - pfree(prefix); - pfree(patt); - } break; case OID_TEXT_ICREGEXEQ_OP: case OID_BPCHAR_ICREGEXEQ_OP: case OID_VARCHAR_ICREGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: if (locale_is_like_safe()) - { - /* the right-hand const is type text for all of these */ - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, &prefix, &rest) != Pattern_Prefix_None; - if (prefix) - pfree(prefix); - pfree(patt); - } break; case OID_INET_SUB_OP: --- 1704,1748 ---- case OID_BPCHAR_LIKE_OP: case OID_VARCHAR_LIKE_OP: case OID_NAME_LIKE_OP: + /* the right-hand const is type text for all of these */ if (locale_is_like_safe()) isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, &prefix, &rest) != Pattern_Prefix_None; ! break; ! ! case OID_BYTEA_LIKE_OP: ! isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, ! &prefix, &rest) != Pattern_Prefix_None; break; case OID_TEXT_ICLIKE_OP: case OID_BPCHAR_ICLIKE_OP: case OID_VARCHAR_ICLIKE_OP: case OID_NAME_ICLIKE_OP: + /* the right-hand const is type text for all of these */ if (locale_is_like_safe()) isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, &prefix, &rest) != Pattern_Prefix_None; break; case OID_TEXT_REGEXEQ_OP: case OID_BPCHAR_REGEXEQ_OP: case OID_VARCHAR_REGEXEQ_OP: case OID_NAME_REGEXEQ_OP: + /* the right-hand const is type text for all of these */ if (locale_is_like_safe()) isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, &prefix, &rest) != Pattern_Prefix_None; break; case OID_TEXT_ICREGEXEQ_OP: case OID_BPCHAR_ICREGEXEQ_OP: case OID_VARCHAR_ICREGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: + /* the right-hand const is type text for all of these */ if (locale_is_like_safe()) isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, &prefix, &rest) != Pattern_Prefix_None; break; case OID_INET_SUB_OP: *************** *** 1777,1782 **** --- 1753,1764 ---- break; } + if (prefix) + { + pfree(DatumGetPointer(prefix->constvalue)); + pfree(prefix); + } + /* done if the expression doesn't look indexable */ if (!isIndexable) return false; *************** *** 1798,1803 **** --- 1780,1791 ---- isIndexable = false; break; + case OID_BYTEA_LIKE_OP: + if (!op_in_opclass(find_operator(">=", BYTEAOID), opclass) || + !op_in_opclass(find_operator("<", BYTEAOID), opclass)) + isIndexable = false; + break; + case OID_BPCHAR_LIKE_OP: case OID_BPCHAR_ICLIKE_OP: case OID_BPCHAR_REGEXEQ_OP: *************** *** 1867,1876 **** Var *leftop = get_leftop(clause); Var *rightop = get_rightop(clause); Oid expr_op = ((Oper *) clause->oper)->opno; ! Datum constvalue; ! char *patt; ! char *prefix; ! char *rest; Pattern_Prefix_Status pstatus; switch (expr_op) --- 1855,1863 ---- Var *leftop = get_leftop(clause); Var *rightop = get_rightop(clause); Oid expr_op = ((Oper *) clause->oper)->opno; ! Const *patt = (Const *) rightop; ! Const *prefix = NULL; ! Const *rest = NULL; Pattern_Prefix_Status pstatus; switch (expr_op) *************** *** 1885,1902 **** case OID_BPCHAR_LIKE_OP: case OID_VARCHAR_LIKE_OP: case OID_NAME_LIKE_OP: ! /* the right-hand const is type text for all of these */ ! constvalue = ((Const *) rightop)->constvalue; ! patt = DatumGetCString(DirectFunctionCall1(textout, ! constvalue)); pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); - if (prefix) - pfree(prefix); - pfree(patt); break; case OID_TEXT_ICLIKE_OP: --- 1872,1883 ---- case OID_BPCHAR_LIKE_OP: case OID_VARCHAR_LIKE_OP: case OID_NAME_LIKE_OP: ! case OID_BYTEA_LIKE_OP: pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); break; case OID_TEXT_ICLIKE_OP: *************** *** 1904,1920 **** case OID_VARCHAR_ICLIKE_OP: case OID_NAME_ICLIKE_OP: /* the right-hand const is type text for all of these */ - constvalue = ((Const *) rightop)->constvalue; - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); - if (prefix) - pfree(prefix); - pfree(patt); break; case OID_TEXT_REGEXEQ_OP: --- 1885,1895 ---- *************** *** 1922,1938 **** case OID_VARCHAR_REGEXEQ_OP: case OID_NAME_REGEXEQ_OP: /* the right-hand const is type text for all of these */ - constvalue = ((Const *) rightop)->constvalue; - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); - if (prefix) - pfree(prefix); - pfree(patt); break; case OID_TEXT_ICREGEXEQ_OP: --- 1897,1907 ---- *************** *** 1940,1966 **** case OID_VARCHAR_ICREGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: /* the right-hand const is type text for all of these */ - constvalue = ((Const *) rightop)->constvalue; - patt = DatumGetCString(DirectFunctionCall1(textout, - constvalue)); pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); - if (prefix) - pfree(prefix); - pfree(patt); break; case OID_INET_SUB_OP: case OID_INET_SUBEQ_OP: case OID_CIDR_SUB_OP: case OID_CIDR_SUBEQ_OP: - constvalue = ((Const *) rightop)->constvalue; resultquals = nconc(resultquals, network_prefix_quals(leftop, expr_op, ! constvalue)); break; default: --- 1909,1928 ---- case OID_VARCHAR_ICREGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: /* the right-hand const is type text for all of these */ pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, &prefix, &rest); resultquals = nconc(resultquals, prefix_quals(leftop, expr_op, prefix, pstatus)); break; case OID_INET_SUB_OP: case OID_INET_SUBEQ_OP: case OID_CIDR_SUB_OP: case OID_CIDR_SUBEQ_OP: resultquals = nconc(resultquals, network_prefix_quals(leftop, expr_op, ! patt->constvalue)); break; default: *************** *** 1980,1994 **** */ static List * prefix_quals(Var *leftop, Oid expr_op, ! char *prefix, Pattern_Prefix_Status pstatus) { List *result; Oid datatype; Oid oproid; Const *con; Oper *op; Expr *expr; ! char *greaterstr; Assert(pstatus != Pattern_Prefix_None); --- 1942,1957 ---- */ static List * prefix_quals(Var *leftop, Oid expr_op, ! Const *prefix_const, Pattern_Prefix_Status pstatus) { List *result; Oid datatype; Oid oproid; + char *prefix; Const *con; Oper *op; Expr *expr; ! Const *greaterstr = NULL; Assert(pstatus != Pattern_Prefix_None); *************** *** 2001,2006 **** --- 1964,1973 ---- datatype = TEXTOID; break; + case OID_BYTEA_LIKE_OP: + datatype = BYTEAOID; + break; + case OID_BPCHAR_LIKE_OP: case OID_BPCHAR_ICLIKE_OP: case OID_BPCHAR_REGEXEQ_OP: *************** *** 2027,2032 **** --- 1994,2004 ---- return NIL; } + if (prefix_const->consttype != BYTEAOID) + prefix = DatumGetCString(DirectFunctionCall1(textout, prefix_const->constvalue)); + else + prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefix_const->constvalue)); + /* * If we found an exact-match pattern, generate an "=" indexqual. */ *************** *** 2060,2076 **** * "x < greaterstr". *------- */ ! greaterstr = make_greater_string(prefix, datatype); if (greaterstr) { oproid = find_operator("<", datatype); if (oproid == InvalidOid) elog(ERROR, "prefix_quals: no < operator for type %u", datatype); - con = string_to_const(greaterstr, datatype); op = makeOper(oproid, InvalidOid, BOOLOID, false); ! expr = make_opclause(op, leftop, (Var *) con); result = lappend(result, expr); - pfree(greaterstr); } return result; --- 2032,2046 ---- * "x < greaterstr". *------- */ ! greaterstr = make_greater_string(con); if (greaterstr) { oproid = find_operator("<", datatype); if (oproid == InvalidOid) elog(ERROR, "prefix_quals: no < operator for type %u", datatype); op = makeOper(oproid, InvalidOid, BOOLOID, false); ! expr = make_opclause(op, leftop, (Var *) greaterstr); result = lappend(result, expr); } return result; *************** *** 2186,2191 **** --- 2156,2163 ---- */ if (datatype == NAMEOID) return DirectFunctionCall1(namein, CStringGetDatum(str)); + else if (datatype == BYTEAOID) + return DirectFunctionCall1(byteain, CStringGetDatum(str)); else return DirectFunctionCall1(textin, CStringGetDatum(str)); } Index: src/backend/utils/adt/like.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v retrieving revision 1.51 diff -c -r1.51 like.c *** src/backend/utils/adt/like.c 29 Aug 2002 07:22:26 -0000 1.51 --- src/backend/utils/adt/like.c 1 Sep 2002 21:46:27 -0000 *************** *** 242,248 **** bytealike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! text *pat = PG_GETARG_TEXT_P(1); bool result; unsigned char *s, *p; --- 242,248 ---- bytealike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! bytea *pat = PG_GETARG_BYTEA_P(1); bool result; unsigned char *s, *p; *************** *** 263,269 **** byteanlike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! text *pat = PG_GETARG_TEXT_P(1); bool result; unsigned char *s, *p; --- 263,269 ---- byteanlike(PG_FUNCTION_ARGS) { bytea *str = PG_GETARG_BYTEA_P(0); ! bytea *pat = PG_GETARG_BYTEA_P(1); bool result; unsigned char *s, *p; Index: src/backend/utils/adt/selfuncs.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/selfuncs.c,v retrieving revision 1.114 diff -c -r1.114 selfuncs.c *** src/backend/utils/adt/selfuncs.c 29 Aug 2002 07:22:27 -0000 1.114 --- src/backend/utils/adt/selfuncs.c 1 Sep 2002 23:14:57 -0000 *************** *** 73,78 **** --- 73,79 ---- #include <locale.h> #include "access/heapam.h" + #include "access/tuptoaster.h" #include "catalog/catname.h" #include "catalog/pg_namespace.h" #include "catalog/pg_operator.h" *************** *** 168,175 **** Var **var, Node **other, bool *varonleft); static void get_join_vars(List *args, Var **var1, Var **var2); ! static Selectivity prefix_selectivity(Query *root, Var *var, char *prefix); ! static Selectivity pattern_selectivity(char *patt, Pattern_Type ptype); static bool string_lessthan(const char *str1, const char *str2, Oid datatype); static Oid find_operator(const char *opname, Oid datatype); --- 169,176 ---- Var **var, Node **other, bool *varonleft); static void get_join_vars(List *args, Var **var1, Var **var2); ! static Selectivity prefix_selectivity(Query *root, Var *var, Const *prefix); ! static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype); static bool string_lessthan(const char *str1, const char *str2, Oid datatype); static Oid find_operator(const char *opname, Oid datatype); *************** *** 826,835 **** bool varonleft; Oid relid; Datum constval; - char *patt; Pattern_Prefix_Status pstatus; ! char *prefix; ! char *rest; double result; /* --- 827,836 ---- bool varonleft; Oid relid; Datum constval; Pattern_Prefix_Status pstatus; ! Const *patt = NULL; ! Const *prefix = NULL; ! Const *rest = NULL; double result; /* *************** *** 853,863 **** if (((Const *) other)->constisnull) return 0.0; constval = ((Const *) other)->constvalue; ! /* the right-hand const is type text for all supported operators */ ! Assert(((Const *) other)->consttype == TEXTOID); ! patt = DatumGetCString(DirectFunctionCall1(textout, constval)); /* divide pattern into fixed prefix and remainder */ pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); if (pstatus == Pattern_Prefix_Exact) --- 854,866 ---- if (((Const *) other)->constisnull) return 0.0; constval = ((Const *) other)->constvalue; ! ! /* the right-hand const is type text or bytea for all supported operators */ ! Assert(((Const *) other)->consttype == TEXTOID || ! ((Const *) other)->consttype == BYTEAOID); /* divide pattern into fixed prefix and remainder */ + patt = (Const *) other; pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); if (pstatus == Pattern_Prefix_Exact) *************** *** 866,879 **** * Pattern specifies an exact match, so pretend operator is '=' */ Oid eqopr = find_operator("=", var->vartype); - Const *eqcon; List *eqargs; if (eqopr == InvalidOid) elog(ERROR, "patternsel: no = operator for type %u", var->vartype); ! eqcon = string_to_const(prefix, var->vartype); ! eqargs = makeList2(var, eqcon); result = DatumGetFloat8(DirectFunctionCall4(eqsel, PointerGetDatum(root), ObjectIdGetDatum(eqopr), --- 869,880 ---- * Pattern specifies an exact match, so pretend operator is '=' */ Oid eqopr = find_operator("=", var->vartype); List *eqargs; if (eqopr == InvalidOid) elog(ERROR, "patternsel: no = operator for type %u", var->vartype); ! eqargs = makeList2(var, prefix); result = DatumGetFloat8(DirectFunctionCall4(eqsel, PointerGetDatum(root), ObjectIdGetDatum(eqopr), *************** *** 903,910 **** } if (prefix) pfree(prefix); ! pfree(patt); return result; } --- 904,913 ---- } if (prefix) + { + pfree(DatumGetPointer(prefix->constvalue)); pfree(prefix); ! } return result; } *************** *** 2693,2709 **** */ static Pattern_Prefix_Status ! like_fixed_prefix(char *patt, bool case_insensitive, ! char **prefix, char **rest) { char *match; int pos, match_pos; ! *prefix = match = palloc(strlen(patt) + 1); match_pos = 0; ! for (pos = 0; patt[pos]; pos++) { /* % and _ are wildcard characters in LIKE */ if (patt[pos] == '%' || --- 2696,2734 ---- */ static Pattern_Prefix_Status ! like_fixed_prefix(Const *patt_const, bool case_insensitive, ! Const **prefix_const, Const **rest_const) { char *match; + char *patt; + int pattlen; + char *prefix; + char *rest; + Oid typeid = patt_const->consttype; int pos, match_pos; ! /* the right-hand const is type text or bytea */ ! Assert(typeid == BYTEAOID || typeid == TEXTOID); ! ! if (typeid == BYTEAOID && case_insensitive) ! elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); ! ! if (typeid != BYTEAOID) ! { ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); ! pattlen = strlen(patt); ! } ! else ! { ! patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); ! pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; ! } ! ! prefix = match = palloc(pattlen + 1); match_pos = 0; ! for (pos = 0; pos < pattlen; pos++) { /* % and _ are wildcard characters in LIKE */ if (patt[pos] == '%' || *************** *** 2713,2719 **** if (patt[pos] == '\\') { pos++; ! if (patt[pos] == '\0') break; } --- 2738,2744 ---- if (patt[pos] == '\\') { pos++; ! if (patt[pos] == '\0' && typeid != BYTEAOID) break; } *************** *** 2733,2767 **** } match[match_pos] = '\0'; ! *rest = &patt[pos]; /* in LIKE, an empty pattern is an exact match! */ ! if (patt[pos] == '\0') return Pattern_Prefix_Exact; /* reached end of pattern, so * exact */ if (match_pos > 0) return Pattern_Prefix_Partial; - pfree(match); - *prefix = NULL; return Pattern_Prefix_None; } static Pattern_Prefix_Status ! regex_fixed_prefix(char *patt, bool case_insensitive, ! char **prefix, char **rest) { char *match; int pos, match_pos, paren_depth; /* Pattern must be anchored left */ if (patt[0] != '^') { ! *prefix = NULL; ! *rest = patt; return Pattern_Prefix_None; } --- 2758,2815 ---- } match[match_pos] = '\0'; ! rest = &patt[pos]; ! ! *prefix_const = string_to_const(prefix, typeid); ! *rest_const = string_to_const(rest, typeid); ! ! pfree(patt); ! pfree(match); ! prefix = NULL; /* in LIKE, an empty pattern is an exact match! */ ! if (pos == pattlen) return Pattern_Prefix_Exact; /* reached end of pattern, so * exact */ if (match_pos > 0) return Pattern_Prefix_Partial; return Pattern_Prefix_None; } static Pattern_Prefix_Status ! regex_fixed_prefix(Const *patt_const, bool case_insensitive, ! Const **prefix_const, Const **rest_const) { char *match; int pos, match_pos, paren_depth; + char *patt; + char *prefix; + char *rest; + Oid typeid = patt_const->consttype; + + /* + * Should be unnecessary, there are no bytea regex operators defined. + * As such, it should be noted that the rest of this function has *not* + * been made safe for binary (possibly NULL containing) strings. + */ + if (typeid == BYTEAOID) + elog(ERROR, "Regex matching not supported on type BYTEA"); + + /* the right-hand const is type text for all of these */ + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); /* Pattern must be anchored left */ if (patt[0] != '^') { ! rest = patt; ! ! *prefix_const = NULL; ! *rest_const = string_to_const(rest, typeid); ! return Pattern_Prefix_None; } *************** *** 2774,2781 **** { if (patt[pos] == '|' && paren_depth == 0) { ! *prefix = NULL; ! *rest = patt; return Pattern_Prefix_None; } else if (patt[pos] == '(') --- 2822,2832 ---- { if (patt[pos] == '|' && paren_depth == 0) { ! rest = patt; ! ! *prefix_const = NULL; ! *rest_const = string_to_const(rest, typeid); ! return Pattern_Prefix_None; } else if (patt[pos] == '(') *************** *** 2792,2798 **** } /* OK, allocate space for pattern */ ! *prefix = match = palloc(strlen(patt) + 1); match_pos = 0; /* note start at pos 1 to skip leading ^ */ --- 2843,2849 ---- } /* OK, allocate space for pattern */ ! prefix = match = palloc(strlen(patt) + 1); match_pos = 0; /* note start at pos 1 to skip leading ^ */ *************** *** 2841,2865 **** } match[match_pos] = '\0'; ! *rest = &patt[pos]; if (patt[pos] == '$' && patt[pos + 1] == '\0') { ! *rest = &patt[pos + 1]; return Pattern_Prefix_Exact; /* pattern specifies exact match */ } if (match_pos > 0) return Pattern_Prefix_Partial; - pfree(match); - *prefix = NULL; return Pattern_Prefix_None; } Pattern_Prefix_Status ! pattern_fixed_prefix(char *patt, Pattern_Type ptype, ! char **prefix, char **rest) { Pattern_Prefix_Status result; --- 2892,2925 ---- } match[match_pos] = '\0'; ! rest = &patt[pos]; if (patt[pos] == '$' && patt[pos + 1] == '\0') { ! rest = &patt[pos + 1]; ! ! *prefix_const = string_to_const(prefix, typeid); ! *rest_const = string_to_const(rest, typeid); ! return Pattern_Prefix_Exact; /* pattern specifies exact match */ } + *prefix_const = string_to_const(prefix, typeid); + *rest_const = string_to_const(rest, typeid); + + pfree(patt); + pfree(match); + prefix = NULL; + if (match_pos > 0) return Pattern_Prefix_Partial; return Pattern_Prefix_None; } Pattern_Prefix_Status ! pattern_fixed_prefix(Const *patt, Pattern_Type ptype, ! Const **prefix, Const **rest) { Pattern_Prefix_Status result; *************** *** 2897,2915 **** * more useful to use the upper-bound code than not. */ static Selectivity ! prefix_selectivity(Query *root, Var *var, char *prefix) { Selectivity prefixsel; Oid cmpopr; ! Const *prefixcon; List *cmpargs; ! char *greaterstr; cmpopr = find_operator(">=", var->vartype); if (cmpopr == InvalidOid) elog(ERROR, "prefix_selectivity: no >= operator for type %u", var->vartype); ! prefixcon = string_to_const(prefix, var->vartype); cmpargs = makeList2(var, prefixcon); /* Assume scalargtsel is appropriate for all supported types */ prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, --- 2957,2979 ---- * more useful to use the upper-bound code than not. */ static Selectivity ! prefix_selectivity(Query *root, Var *var, Const *prefixcon) { Selectivity prefixsel; Oid cmpopr; ! char *prefix; List *cmpargs; ! Const *greaterstrcon; cmpopr = find_operator(">=", var->vartype); if (cmpopr == InvalidOid) elog(ERROR, "prefix_selectivity: no >= operator for type %u", var->vartype); ! if (prefixcon->consttype != BYTEAOID) ! prefix = DatumGetCString(DirectFunctionCall1(textout, prefixcon->constvalue)); ! else ! prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefixcon->constvalue)); ! cmpargs = makeList2(var, prefixcon); /* Assume scalargtsel is appropriate for all supported types */ prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, *************** *** 2923,2930 **** * "x < greaterstr". *------- */ ! greaterstr = make_greater_string(prefix, var->vartype); ! if (greaterstr) { Selectivity topsel; --- 2987,2994 ---- * "x < greaterstr". *------- */ ! greaterstrcon = make_greater_string(prefixcon); ! if (greaterstrcon) { Selectivity topsel; *************** *** 2932,2939 **** if (cmpopr == InvalidOid) elog(ERROR, "prefix_selectivity: no < operator for type %u", var->vartype); ! prefixcon = string_to_const(greaterstr, var->vartype); ! cmpargs = makeList2(var, prefixcon); /* Assume scalarltsel is appropriate for all supported types */ topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, PointerGetDatum(root), --- 2996,3002 ---- if (cmpopr == InvalidOid) elog(ERROR, "prefix_selectivity: no < operator for type %u", var->vartype); ! cmpargs = makeList2(var, greaterstrcon); /* Assume scalarltsel is appropriate for all supported types */ topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, PointerGetDatum(root), *************** *** 2997,3010 **** #define PARTIAL_WILDCARD_SEL 2.0 static Selectivity ! like_selectivity(char *patt, bool case_insensitive) { Selectivity sel = 1.0; int pos; /* Skip any leading %; it's already factored into initial sel */ ! pos = (*patt == '%') ? 1 : 0; ! for (; patt[pos]; pos++) { /* % and _ are wildcard characters in LIKE */ if (patt[pos] == '%') --- 3060,3094 ---- #define PARTIAL_WILDCARD_SEL 2.0 static Selectivity ! like_selectivity(Const *patt_const, bool case_insensitive) { Selectivity sel = 1.0; int pos; + int start; + Oid typeid = patt_const->consttype; + char *patt; + int pattlen; + + /* the right-hand const is type text or bytea */ + Assert(typeid == BYTEAOID || typeid == TEXTOID); + + if (typeid == BYTEAOID && case_insensitive) + elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); + + if (typeid != BYTEAOID) + { + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); + pattlen = strlen(patt); + } + else + { + patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); + pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; + } /* Skip any leading %; it's already factored into initial sel */ ! start = (*patt == '%') ? 1 : 0; ! for (pos = start; pos < pattlen; pos++) { /* % and _ are wildcard characters in LIKE */ if (patt[pos] == '%') *************** *** 3015,3021 **** { /* Backslash quotes the next character */ pos++; ! if (patt[pos] == '\0') break; sel *= FIXED_CHAR_SEL; } --- 3099,3105 ---- { /* Backslash quotes the next character */ pos++; ! if (patt[pos] == '\0' && typeid != BYTEAOID) break; sel *= FIXED_CHAR_SEL; } *************** *** 3122,3131 **** } static Selectivity ! regex_selectivity(char *patt, bool case_insensitive) { Selectivity sel; ! int pattlen = strlen(patt); /* If patt doesn't end with $, consider it to have a trailing wildcard */ if (pattlen > 0 && patt[pattlen - 1] == '$' && --- 3206,3229 ---- } static Selectivity ! regex_selectivity(Const *patt_const, bool case_insensitive) { Selectivity sel; ! char *patt; ! int pattlen; ! Oid typeid = patt_const->consttype; ! ! /* ! * Should be unnecessary, there are no bytea regex operators defined. ! * As such, it should be noted that the rest of this function has *not* ! * been made safe for binary (possibly NULL containing) strings. ! */ ! if (typeid == BYTEAOID) ! elog(ERROR, "Regex matching not supported on type BYTEA"); ! ! /* the right-hand const is type text for all of these */ ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); ! pattlen = strlen(patt); /* If patt doesn't end with $, consider it to have a trailing wildcard */ if (pattlen > 0 && patt[pattlen - 1] == '$' && *************** *** 3146,3152 **** } static Selectivity ! pattern_selectivity(char *patt, Pattern_Type ptype) { Selectivity result; --- 3244,3250 ---- } static Selectivity ! pattern_selectivity(Const *patt, Pattern_Type ptype) { Selectivity result; *************** *** 3220,3238 **** * sort passes, etc. For now, we just shut down the whole thing in locales * that do such things :-( */ ! char * ! make_greater_string(const char *str, Oid datatype) { char *workstr; int len; ! /* ! * Make a modifiable copy, which will be our return value if ! * successful ! */ ! workstr = pstrdup((char *) str); ! while ((len = strlen(workstr)) > 0) { unsigned char *lastchar = (unsigned char *) (workstr + len - 1); --- 3318,3350 ---- * sort passes, etc. For now, we just shut down the whole thing in locales * that do such things :-( */ ! Const * ! make_greater_string(const Const *str_const) { + Oid datatype = str_const->consttype; + char *str; char *workstr; int len; ! /* Get the string and a modifiable copy */ ! if (datatype == NAMEOID) ! { ! str = DatumGetCString(DirectFunctionCall1(nameout, str_const->constvalue)); ! len = strlen(str); ! } ! else if (datatype == BYTEAOID) ! { ! str = DatumGetCString(DirectFunctionCall1(byteaout, str_const->constvalue)); ! len = toast_raw_datum_size(str_const->constvalue) - VARHDRSZ; ! } ! else ! { ! str = DatumGetCString(DirectFunctionCall1(textout, str_const->constvalue)); ! len = strlen(str); ! } ! workstr = pstrdup(str); ! while (len > 0) { unsigned char *lastchar = (unsigned char *) (workstr + len - 1); *************** *** 3243,3262 **** { (*lastchar)++; if (string_lessthan(str, workstr, datatype)) ! return workstr; /* Success! */ } /* * Truncate off the last character, which might be more than 1 * byte in MULTIBYTE case. */ ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); ! workstr[len] = '\0'; } /* Failed... */ pfree(workstr); ! return NULL; } /* --- 3355,3388 ---- { (*lastchar)++; if (string_lessthan(str, workstr, datatype)) ! { ! /* Success! */ ! Const *workstr_const = string_to_const(workstr, datatype); ! ! pfree(str); ! pfree(workstr); ! return workstr_const; ! } } /* * Truncate off the last character, which might be more than 1 * byte in MULTIBYTE case. */ ! if (datatype != BYTEAOID && pg_database_encoding_max_length() > 1) ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); ! else ! len -= - 1; ! ! if (datatype != BYTEAOID) ! workstr[len] = '\0'; } /* Failed... */ + pfree(str); pfree(workstr); ! ! return (Const *) NULL; } /* *************** *** 3330,3341 **** --- 3456,3471 ---- static Datum string_to_datum(const char *str, Oid datatype) { + Assert(str != NULL); + /* * We cheat a little by assuming that textin() will do for bpchar and * varchar constants too... */ if (datatype == NAMEOID) return DirectFunctionCall1(namein, CStringGetDatum(str)); + else if (datatype == BYTEAOID) + return DirectFunctionCall1(byteain, CStringGetDatum(str)); else return DirectFunctionCall1(textin, CStringGetDatum(str)); } Index: src/include/catalog/pg_operator.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v retrieving revision 1.107 diff -c -r1.107 pg_operator.h *** src/include/catalog/pg_operator.h 22 Aug 2002 04:45:11 -0000 1.107 --- src/include/catalog/pg_operator.h 1 Sep 2002 21:46:27 -0000 *************** *** 827,835 **** DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); #define OID_BYTEA_LIKE_OP 2016 ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); /* timestamp operators */ --- 827,835 ---- DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); #define OID_BYTEA_LIKE_OP 2016 ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); /* timestamp operators */ Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v retrieving revision 1.267 diff -c -r1.267 pg_proc.h *** src/include/catalog/pg_proc.h 1 Sep 2002 00:58:06 -0000 1.267 --- src/include/catalog/pg_proc.h 1 Sep 2002 21:46:27 -0000 *************** *** 2770,2782 **** DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); DESCR("adjust time with time zone precision"); ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); DESCR("does not match LIKE expression"); ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); DESCR("does not match LIKE expression"); DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); DESCR("convert match pattern to use backslash escapes"); --- 2770,2782 ---- DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); DESCR("adjust time with time zone precision"); ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); DESCR("does not match LIKE expression"); ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); DESCR("matches LIKE expression"); ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); DESCR("does not match LIKE expression"); DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); DESCR("convert match pattern to use backslash escapes"); Index: src/include/utils/selfuncs.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/selfuncs.h,v retrieving revision 1.6 diff -c -r1.6 selfuncs.h *** src/include/utils/selfuncs.h 20 Jun 2002 20:29:53 -0000 1.6 --- src/include/utils/selfuncs.h 1 Sep 2002 21:46:27 -0000 *************** *** 33,44 **** /* selfuncs.c */ ! extern Pattern_Prefix_Status pattern_fixed_prefix(char *patt, Pattern_Type ptype, ! char **prefix, ! char **rest); extern bool locale_is_like_safe(void); ! extern char *make_greater_string(const char *str, Oid datatype); extern Datum eqsel(PG_FUNCTION_ARGS); extern Datum neqsel(PG_FUNCTION_ARGS); --- 33,44 ---- /* selfuncs.c */ ! extern Pattern_Prefix_Status pattern_fixed_prefix(Const *patt, Pattern_Type ptype, ! Const **prefix, ! Const **rest); extern bool locale_is_like_safe(void); ! extern Const *make_greater_string(const Const *str_const); extern Datum eqsel(PG_FUNCTION_ARGS); extern Datum neqsel(PG_FUNCTION_ARGS);
Alvar Freude wrote: > With this, the limits in Bytea indexes are removed? Great! Depends on what you mean by limits, but yes, it does enable the optimizer to use an index given: ... WHERE bytea_field LIKE 'abc%'; and an index on "bytea_field". > As far is I followed the discussion, there where some additionsl limits > on bytea indexes (and like); or are they removed with this patch? Not sure what you're referring to here. The patch I sent in a few weeks ago (and which was applied) did impose a limit of sorts in that it forces the 'abc%' above to be cast as text, which would make it impossible to search for a pattern which includes a '\0' character, for example. > I'll try it sometime later and make some performance checks -- in the > hopem that bytea is faster then texts ;-) (at least the base 255 encoding > i can throw out of my application). Note that there is no guarantee that this will get applied to cvs for 7.3. The freeze for 7.3 beta is supposed to be tonight, and this patch will need some review, as it is non-trivial. Joe
Your patch has been added to the PostgreSQL unapplied patches list at: http://207.106.42.251/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Joe Conway wrote: > Tom Lane wrote: > > Joe Conway <mail@joeconway.com> writes: > >>OK. I'll look at both options and make another diff -c proposal ;-) Once > >>that's resolved I'll go back to original issue Alvar raised. > > > > Okay. When you get back to the original issue, the gold is hidden in > > src/backend/optimizer/path/indxpath.c; see the "special indexable > > operators" stuff near the bottom of that file. (It's a bit of a crock > > that this code is hardwired there, and not somehow accessed through a > > system catalog, but it's what we've got at the moment.) > > The attached patch re-enables a bytea right hand argument (as compared > to a text right hand argument), and enables index usage, for bytea LIKE > -- e.g.: > > > parts=# explain select * from bombytea where parent_part like '05-05%'; > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using bombytea_idx1 on bombytea (cost=0.00..3479.67 > rows=1118 width=34) > Index Cond: ((parent_part >= '05-05'::bytea) AND (parent_part < > '05-06'::bytea)) > Filter: (parent_part ~~ '05-05%'::bytea) > (3 rows) > > > Passes all regression tests, and as far as I can tell does not break or > change the behavior of anything else. Please review and apply if there > are no objections (I'd like to see this applied for 7.3, before the > freeze, if possible, but I'll certainly understand if I'm told there's > not enough time left). > > Thanks, > > Joe > Index: src/backend/optimizer/path/indxpath.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/path/indxpath.c,v > retrieving revision 1.120 > diff -c -r1.120 indxpath.c > *** src/backend/optimizer/path/indxpath.c 13 Jul 2002 19:20:34 -0000 1.120 > --- src/backend/optimizer/path/indxpath.c 1 Sep 2002 22:19:16 -0000 > *************** > *** 97,103 **** > static bool match_special_index_operator(Expr *clause, Oid opclass, > bool indexkey_on_left); > static List *prefix_quals(Var *leftop, Oid expr_op, > ! char *prefix, Pattern_Prefix_Status pstatus); > static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); > static Oid find_operator(const char *opname, Oid datatype); > static Datum string_to_datum(const char *str, Oid datatype); > --- 97,103 ---- > static bool match_special_index_operator(Expr *clause, Oid opclass, > bool indexkey_on_left); > static List *prefix_quals(Var *leftop, Oid expr_op, > ! Const *prefix, Pattern_Prefix_Status pstatus); > static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); > static Oid find_operator(const char *opname, Oid datatype); > static Datum string_to_datum(const char *str, Oid datatype); > *************** > *** 1675,1684 **** > Var *leftop, > *rightop; > Oid expr_op; > ! Datum constvalue; > ! char *patt; > ! char *prefix; > ! char *rest; > > /* > * Currently, all known special operators require the indexkey on the > --- 1675,1683 ---- > Var *leftop, > *rightop; > Oid expr_op; > ! Const *patt = NULL; > ! Const *prefix = NULL; > ! Const *rest = NULL; > > /* > * Currently, all known special operators require the indexkey on the > *************** > *** 1697,1703 **** > if (!IsA(rightop, Const) || > ((Const *) rightop)->constisnull) > return false; > ! constvalue = ((Const *) rightop)->constvalue; > > switch (expr_op) > { > --- 1696,1702 ---- > if (!IsA(rightop, Const) || > ((Const *) rightop)->constisnull) > return false; > ! patt = (Const *) rightop; > > switch (expr_op) > { > *************** > *** 1705,1772 **** > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest) != Pattern_Prefix_None; > ! if (prefix) > ! pfree(prefix); > ! pfree(patt); > ! } > break; > > case OID_TEXT_ICLIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_TEXT_REGEXEQ_OP: > case OID_BPCHAR_REGEXEQ_OP: > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_TEXT_ICREGEXEQ_OP: > case OID_BPCHAR_ICREGEXEQ_OP: > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_INET_SUB_OP: > --- 1704,1748 ---- > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest) != Pattern_Prefix_None; > ! break; > ! > ! case OID_BYTEA_LIKE_OP: > ! isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > ! &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_ICLIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_REGEXEQ_OP: > case OID_BPCHAR_REGEXEQ_OP: > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_ICREGEXEQ_OP: > case OID_BPCHAR_ICREGEXEQ_OP: > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_INET_SUB_OP: > *************** > *** 1777,1782 **** > --- 1753,1764 ---- > break; > } > > + if (prefix) > + { > + pfree(DatumGetPointer(prefix->constvalue)); > + pfree(prefix); > + } > + > /* done if the expression doesn't look indexable */ > if (!isIndexable) > return false; > *************** > *** 1798,1803 **** > --- 1780,1791 ---- > isIndexable = false; > break; > > + case OID_BYTEA_LIKE_OP: > + if (!op_in_opclass(find_operator(">=", BYTEAOID), opclass) || > + !op_in_opclass(find_operator("<", BYTEAOID), opclass)) > + isIndexable = false; > + break; > + > case OID_BPCHAR_LIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_BPCHAR_REGEXEQ_OP: > *************** > *** 1867,1876 **** > Var *leftop = get_leftop(clause); > Var *rightop = get_rightop(clause); > Oid expr_op = ((Oper *) clause->oper)->opno; > ! Datum constvalue; > ! char *patt; > ! char *prefix; > ! char *rest; > Pattern_Prefix_Status pstatus; > > switch (expr_op) > --- 1855,1863 ---- > Var *leftop = get_leftop(clause); > Var *rightop = get_rightop(clause); > Oid expr_op = ((Oper *) clause->oper)->opno; > ! Const *patt = (Const *) rightop; > ! Const *prefix = NULL; > ! Const *rest = NULL; > Pattern_Prefix_Status pstatus; > > switch (expr_op) > *************** > *** 1885,1902 **** > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > ! /* the right-hand const is type text for all of these */ > ! constvalue = ((Const *) rightop)->constvalue; > ! patt = DatumGetCString(DirectFunctionCall1(textout, > ! constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_ICLIKE_OP: > --- 1872,1883 ---- > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > ! case OID_BYTEA_LIKE_OP: > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > break; > > case OID_TEXT_ICLIKE_OP: > *************** > *** 1904,1920 **** > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_REGEXEQ_OP: > --- 1885,1895 ---- > *************** > *** 1922,1938 **** > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_ICREGEXEQ_OP: > --- 1897,1907 ---- > *************** > *** 1940,1966 **** > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_INET_SUB_OP: > case OID_INET_SUBEQ_OP: > case OID_CIDR_SUB_OP: > case OID_CIDR_SUBEQ_OP: > - constvalue = ((Const *) rightop)->constvalue; > resultquals = nconc(resultquals, > network_prefix_quals(leftop, expr_op, > ! constvalue)); > break; > > default: > --- 1909,1928 ---- > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > /* the right-hand const is type text for all of these */ > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > break; > > case OID_INET_SUB_OP: > case OID_INET_SUBEQ_OP: > case OID_CIDR_SUB_OP: > case OID_CIDR_SUBEQ_OP: > resultquals = nconc(resultquals, > network_prefix_quals(leftop, expr_op, > ! patt->constvalue)); > break; > > default: > *************** > *** 1980,1994 **** > */ > static List * > prefix_quals(Var *leftop, Oid expr_op, > ! char *prefix, Pattern_Prefix_Status pstatus) > { > List *result; > Oid datatype; > Oid oproid; > Const *con; > Oper *op; > Expr *expr; > ! char *greaterstr; > > Assert(pstatus != Pattern_Prefix_None); > > --- 1942,1957 ---- > */ > static List * > prefix_quals(Var *leftop, Oid expr_op, > ! Const *prefix_const, Pattern_Prefix_Status pstatus) > { > List *result; > Oid datatype; > Oid oproid; > + char *prefix; > Const *con; > Oper *op; > Expr *expr; > ! Const *greaterstr = NULL; > > Assert(pstatus != Pattern_Prefix_None); > > *************** > *** 2001,2006 **** > --- 1964,1973 ---- > datatype = TEXTOID; > break; > > + case OID_BYTEA_LIKE_OP: > + datatype = BYTEAOID; > + break; > + > case OID_BPCHAR_LIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_BPCHAR_REGEXEQ_OP: > *************** > *** 2027,2032 **** > --- 1994,2004 ---- > return NIL; > } > > + if (prefix_const->consttype != BYTEAOID) > + prefix = DatumGetCString(DirectFunctionCall1(textout, prefix_const->constvalue)); > + else > + prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefix_const->constvalue)); > + > /* > * If we found an exact-match pattern, generate an "=" indexqual. > */ > *************** > *** 2060,2076 **** > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(prefix, datatype); > if (greaterstr) > { > oproid = find_operator("<", datatype); > if (oproid == InvalidOid) > elog(ERROR, "prefix_quals: no < operator for type %u", datatype); > - con = string_to_const(greaterstr, datatype); > op = makeOper(oproid, InvalidOid, BOOLOID, false); > ! expr = make_opclause(op, leftop, (Var *) con); > result = lappend(result, expr); > - pfree(greaterstr); > } > > return result; > --- 2032,2046 ---- > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(con); > if (greaterstr) > { > oproid = find_operator("<", datatype); > if (oproid == InvalidOid) > elog(ERROR, "prefix_quals: no < operator for type %u", datatype); > op = makeOper(oproid, InvalidOid, BOOLOID, false); > ! expr = make_opclause(op, leftop, (Var *) greaterstr); > result = lappend(result, expr); > } > > return result; > *************** > *** 2186,2191 **** > --- 2156,2163 ---- > */ > if (datatype == NAMEOID) > return DirectFunctionCall1(namein, CStringGetDatum(str)); > + else if (datatype == BYTEAOID) > + return DirectFunctionCall1(byteain, CStringGetDatum(str)); > else > return DirectFunctionCall1(textin, CStringGetDatum(str)); > } > Index: src/backend/utils/adt/like.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v > retrieving revision 1.51 > diff -c -r1.51 like.c > *** src/backend/utils/adt/like.c 29 Aug 2002 07:22:26 -0000 1.51 > --- src/backend/utils/adt/like.c 1 Sep 2002 21:46:27 -0000 > *************** > *** 242,248 **** > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > --- 242,248 ---- > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > *************** > *** 263,269 **** > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > --- 263,269 ---- > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > Index: src/backend/utils/adt/selfuncs.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/selfuncs.c,v > retrieving revision 1.114 > diff -c -r1.114 selfuncs.c > *** src/backend/utils/adt/selfuncs.c 29 Aug 2002 07:22:27 -0000 1.114 > --- src/backend/utils/adt/selfuncs.c 1 Sep 2002 23:14:57 -0000 > *************** > *** 73,78 **** > --- 73,79 ---- > #include <locale.h> > > #include "access/heapam.h" > + #include "access/tuptoaster.h" > #include "catalog/catname.h" > #include "catalog/pg_namespace.h" > #include "catalog/pg_operator.h" > *************** > *** 168,175 **** > Var **var, Node **other, > bool *varonleft); > static void get_join_vars(List *args, Var **var1, Var **var2); > ! static Selectivity prefix_selectivity(Query *root, Var *var, char *prefix); > ! static Selectivity pattern_selectivity(char *patt, Pattern_Type ptype); > static bool string_lessthan(const char *str1, const char *str2, > Oid datatype); > static Oid find_operator(const char *opname, Oid datatype); > --- 169,176 ---- > Var **var, Node **other, > bool *varonleft); > static void get_join_vars(List *args, Var **var1, Var **var2); > ! static Selectivity prefix_selectivity(Query *root, Var *var, Const *prefix); > ! static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype); > static bool string_lessthan(const char *str1, const char *str2, > Oid datatype); > static Oid find_operator(const char *opname, Oid datatype); > *************** > *** 826,835 **** > bool varonleft; > Oid relid; > Datum constval; > - char *patt; > Pattern_Prefix_Status pstatus; > ! char *prefix; > ! char *rest; > double result; > > /* > --- 827,836 ---- > bool varonleft; > Oid relid; > Datum constval; > Pattern_Prefix_Status pstatus; > ! Const *patt = NULL; > ! Const *prefix = NULL; > ! Const *rest = NULL; > double result; > > /* > *************** > *** 853,863 **** > if (((Const *) other)->constisnull) > return 0.0; > constval = ((Const *) other)->constvalue; > ! /* the right-hand const is type text for all supported operators */ > ! Assert(((Const *) other)->consttype == TEXTOID); > ! patt = DatumGetCString(DirectFunctionCall1(textout, constval)); > > /* divide pattern into fixed prefix and remainder */ > pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); > > if (pstatus == Pattern_Prefix_Exact) > --- 854,866 ---- > if (((Const *) other)->constisnull) > return 0.0; > constval = ((Const *) other)->constvalue; > ! > ! /* the right-hand const is type text or bytea for all supported operators */ > ! Assert(((Const *) other)->consttype == TEXTOID || > ! ((Const *) other)->consttype == BYTEAOID); > > /* divide pattern into fixed prefix and remainder */ > + patt = (Const *) other; > pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); > > if (pstatus == Pattern_Prefix_Exact) > *************** > *** 866,879 **** > * Pattern specifies an exact match, so pretend operator is '=' > */ > Oid eqopr = find_operator("=", var->vartype); > - Const *eqcon; > List *eqargs; > > if (eqopr == InvalidOid) > elog(ERROR, "patternsel: no = operator for type %u", > var->vartype); > ! eqcon = string_to_const(prefix, var->vartype); > ! eqargs = makeList2(var, eqcon); > result = DatumGetFloat8(DirectFunctionCall4(eqsel, > PointerGetDatum(root), > ObjectIdGetDatum(eqopr), > --- 869,880 ---- > * Pattern specifies an exact match, so pretend operator is '=' > */ > Oid eqopr = find_operator("=", var->vartype); > List *eqargs; > > if (eqopr == InvalidOid) > elog(ERROR, "patternsel: no = operator for type %u", > var->vartype); > ! eqargs = makeList2(var, prefix); > result = DatumGetFloat8(DirectFunctionCall4(eqsel, > PointerGetDatum(root), > ObjectIdGetDatum(eqopr), > *************** > *** 903,910 **** > } > > if (prefix) > pfree(prefix); > ! pfree(patt); > > return result; > } > --- 904,913 ---- > } > > if (prefix) > + { > + pfree(DatumGetPointer(prefix->constvalue)); > pfree(prefix); > ! } > > return result; > } > *************** > *** 2693,2709 **** > */ > > static Pattern_Prefix_Status > ! like_fixed_prefix(char *patt, bool case_insensitive, > ! char **prefix, char **rest) > { > char *match; > int pos, > match_pos; > > ! *prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > ! for (pos = 0; patt[pos]; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%' || > --- 2696,2734 ---- > */ > > static Pattern_Prefix_Status > ! like_fixed_prefix(Const *patt_const, bool case_insensitive, > ! Const **prefix_const, Const **rest_const) > { > char *match; > + char *patt; > + int pattlen; > + char *prefix; > + char *rest; > + Oid typeid = patt_const->consttype; > int pos, > match_pos; > > ! /* the right-hand const is type text or bytea */ > ! Assert(typeid == BYTEAOID || typeid == TEXTOID); > ! > ! if (typeid == BYTEAOID && case_insensitive) > ! elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); > ! > ! if (typeid != BYTEAOID) > ! { > ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > ! pattlen = strlen(patt); > ! } > ! else > ! { > ! patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); > ! pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; > ! } > ! > ! prefix = match = palloc(pattlen + 1); > match_pos = 0; > > ! for (pos = 0; pos < pattlen; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%' || > *************** > *** 2713,2719 **** > if (patt[pos] == '\\') > { > pos++; > ! if (patt[pos] == '\0') > break; > } > > --- 2738,2744 ---- > if (patt[pos] == '\\') > { > pos++; > ! if (patt[pos] == '\0' && typeid != BYTEAOID) > break; > } > > *************** > *** 2733,2767 **** > } > > match[match_pos] = '\0'; > ! *rest = &patt[pos]; > > /* in LIKE, an empty pattern is an exact match! */ > ! if (patt[pos] == '\0') > return Pattern_Prefix_Exact; /* reached end of pattern, so > * exact */ > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > - pfree(match); > - *prefix = NULL; > return Pattern_Prefix_None; > } > > static Pattern_Prefix_Status > ! regex_fixed_prefix(char *patt, bool case_insensitive, > ! char **prefix, char **rest) > { > char *match; > int pos, > match_pos, > paren_depth; > > /* Pattern must be anchored left */ > if (patt[0] != '^') > { > ! *prefix = NULL; > ! *rest = patt; > return Pattern_Prefix_None; > } > > --- 2758,2815 ---- > } > > match[match_pos] = '\0'; > ! rest = &patt[pos]; > ! > ! *prefix_const = string_to_const(prefix, typeid); > ! *rest_const = string_to_const(rest, typeid); > ! > ! pfree(patt); > ! pfree(match); > ! prefix = NULL; > > /* in LIKE, an empty pattern is an exact match! */ > ! if (pos == pattlen) > return Pattern_Prefix_Exact; /* reached end of pattern, so > * exact */ > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > return Pattern_Prefix_None; > } > > static Pattern_Prefix_Status > ! regex_fixed_prefix(Const *patt_const, bool case_insensitive, > ! Const **prefix_const, Const **rest_const) > { > char *match; > int pos, > match_pos, > paren_depth; > + char *patt; > + char *prefix; > + char *rest; > + Oid typeid = patt_const->consttype; > + > + /* > + * Should be unnecessary, there are no bytea regex operators defined. > + * As such, it should be noted that the rest of this function has *not* > + * been made safe for binary (possibly NULL containing) strings. > + */ > + if (typeid == BYTEAOID) > + elog(ERROR, "Regex matching not supported on type BYTEA"); > + > + /* the right-hand const is type text for all of these */ > + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > > /* Pattern must be anchored left */ > if (patt[0] != '^') > { > ! rest = patt; > ! > ! *prefix_const = NULL; > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_None; > } > > *************** > *** 2774,2781 **** > { > if (patt[pos] == '|' && paren_depth == 0) > { > ! *prefix = NULL; > ! *rest = patt; > return Pattern_Prefix_None; > } > else if (patt[pos] == '(') > --- 2822,2832 ---- > { > if (patt[pos] == '|' && paren_depth == 0) > { > ! rest = patt; > ! > ! *prefix_const = NULL; > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_None; > } > else if (patt[pos] == '(') > *************** > *** 2792,2798 **** > } > > /* OK, allocate space for pattern */ > ! *prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > /* note start at pos 1 to skip leading ^ */ > --- 2843,2849 ---- > } > > /* OK, allocate space for pattern */ > ! prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > /* note start at pos 1 to skip leading ^ */ > *************** > *** 2841,2865 **** > } > > match[match_pos] = '\0'; > ! *rest = &patt[pos]; > > if (patt[pos] == '$' && patt[pos + 1] == '\0') > { > ! *rest = &patt[pos + 1]; > return Pattern_Prefix_Exact; /* pattern specifies exact match */ > } > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > - pfree(match); > - *prefix = NULL; > return Pattern_Prefix_None; > } > > Pattern_Prefix_Status > ! pattern_fixed_prefix(char *patt, Pattern_Type ptype, > ! char **prefix, char **rest) > { > Pattern_Prefix_Status result; > > --- 2892,2925 ---- > } > > match[match_pos] = '\0'; > ! rest = &patt[pos]; > > if (patt[pos] == '$' && patt[pos + 1] == '\0') > { > ! rest = &patt[pos + 1]; > ! > ! *prefix_const = string_to_const(prefix, typeid); > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_Exact; /* pattern specifies exact match */ > } > > + *prefix_const = string_to_const(prefix, typeid); > + *rest_const = string_to_const(rest, typeid); > + > + pfree(patt); > + pfree(match); > + prefix = NULL; > + > if (match_pos > 0) > return Pattern_Prefix_Partial; > > return Pattern_Prefix_None; > } > > Pattern_Prefix_Status > ! pattern_fixed_prefix(Const *patt, Pattern_Type ptype, > ! Const **prefix, Const **rest) > { > Pattern_Prefix_Status result; > > *************** > *** 2897,2915 **** > * more useful to use the upper-bound code than not. > */ > static Selectivity > ! prefix_selectivity(Query *root, Var *var, char *prefix) > { > Selectivity prefixsel; > Oid cmpopr; > ! Const *prefixcon; > List *cmpargs; > ! char *greaterstr; > > cmpopr = find_operator(">=", var->vartype); > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no >= operator for type %u", > var->vartype); > ! prefixcon = string_to_const(prefix, var->vartype); > cmpargs = makeList2(var, prefixcon); > /* Assume scalargtsel is appropriate for all supported types */ > prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, > --- 2957,2979 ---- > * more useful to use the upper-bound code than not. > */ > static Selectivity > ! prefix_selectivity(Query *root, Var *var, Const *prefixcon) > { > Selectivity prefixsel; > Oid cmpopr; > ! char *prefix; > List *cmpargs; > ! Const *greaterstrcon; > > cmpopr = find_operator(">=", var->vartype); > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no >= operator for type %u", > var->vartype); > ! if (prefixcon->consttype != BYTEAOID) > ! prefix = DatumGetCString(DirectFunctionCall1(textout, prefixcon->constvalue)); > ! else > ! prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefixcon->constvalue)); > ! > cmpargs = makeList2(var, prefixcon); > /* Assume scalargtsel is appropriate for all supported types */ > prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, > *************** > *** 2923,2930 **** > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(prefix, var->vartype); > ! if (greaterstr) > { > Selectivity topsel; > > --- 2987,2994 ---- > * "x < greaterstr". > *------- > */ > ! greaterstrcon = make_greater_string(prefixcon); > ! if (greaterstrcon) > { > Selectivity topsel; > > *************** > *** 2932,2939 **** > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no < operator for type %u", > var->vartype); > ! prefixcon = string_to_const(greaterstr, var->vartype); > ! cmpargs = makeList2(var, prefixcon); > /* Assume scalarltsel is appropriate for all supported types */ > topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, > PointerGetDatum(root), > --- 2996,3002 ---- > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no < operator for type %u", > var->vartype); > ! cmpargs = makeList2(var, greaterstrcon); > /* Assume scalarltsel is appropriate for all supported types */ > topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, > PointerGetDatum(root), > *************** > *** 2997,3010 **** > #define PARTIAL_WILDCARD_SEL 2.0 > > static Selectivity > ! like_selectivity(char *patt, bool case_insensitive) > { > Selectivity sel = 1.0; > int pos; > > /* Skip any leading %; it's already factored into initial sel */ > ! pos = (*patt == '%') ? 1 : 0; > ! for (; patt[pos]; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%') > --- 3060,3094 ---- > #define PARTIAL_WILDCARD_SEL 2.0 > > static Selectivity > ! like_selectivity(Const *patt_const, bool case_insensitive) > { > Selectivity sel = 1.0; > int pos; > + int start; > + Oid typeid = patt_const->consttype; > + char *patt; > + int pattlen; > + > + /* the right-hand const is type text or bytea */ > + Assert(typeid == BYTEAOID || typeid == TEXTOID); > + > + if (typeid == BYTEAOID && case_insensitive) > + elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); > + > + if (typeid != BYTEAOID) > + { > + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > + pattlen = strlen(patt); > + } > + else > + { > + patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); > + pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; > + } > > /* Skip any leading %; it's already factored into initial sel */ > ! start = (*patt == '%') ? 1 : 0; > ! for (pos = start; pos < pattlen; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%') > *************** > *** 3015,3021 **** > { > /* Backslash quotes the next character */ > pos++; > ! if (patt[pos] == '\0') > break; > sel *= FIXED_CHAR_SEL; > } > --- 3099,3105 ---- > { > /* Backslash quotes the next character */ > pos++; > ! if (patt[pos] == '\0' && typeid != BYTEAOID) > break; > sel *= FIXED_CHAR_SEL; > } > *************** > *** 3122,3131 **** > } > > static Selectivity > ! regex_selectivity(char *patt, bool case_insensitive) > { > Selectivity sel; > ! int pattlen = strlen(patt); > > /* If patt doesn't end with $, consider it to have a trailing wildcard */ > if (pattlen > 0 && patt[pattlen - 1] == '$' && > --- 3206,3229 ---- > } > > static Selectivity > ! regex_selectivity(Const *patt_const, bool case_insensitive) > { > Selectivity sel; > ! char *patt; > ! int pattlen; > ! Oid typeid = patt_const->consttype; > ! > ! /* > ! * Should be unnecessary, there are no bytea regex operators defined. > ! * As such, it should be noted that the rest of this function has *not* > ! * been made safe for binary (possibly NULL containing) strings. > ! */ > ! if (typeid == BYTEAOID) > ! elog(ERROR, "Regex matching not supported on type BYTEA"); > ! > ! /* the right-hand const is type text for all of these */ > ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > ! pattlen = strlen(patt); > > /* If patt doesn't end with $, consider it to have a trailing wildcard */ > if (pattlen > 0 && patt[pattlen - 1] == '$' && > *************** > *** 3146,3152 **** > } > > static Selectivity > ! pattern_selectivity(char *patt, Pattern_Type ptype) > { > Selectivity result; > > --- 3244,3250 ---- > } > > static Selectivity > ! pattern_selectivity(Const *patt, Pattern_Type ptype) > { > Selectivity result; > > *************** > *** 3220,3238 **** > * sort passes, etc. For now, we just shut down the whole thing in locales > * that do such things :-( > */ > ! char * > ! make_greater_string(const char *str, Oid datatype) > { > char *workstr; > int len; > > ! /* > ! * Make a modifiable copy, which will be our return value if > ! * successful > ! */ > ! workstr = pstrdup((char *) str); > > ! while ((len = strlen(workstr)) > 0) > { > unsigned char *lastchar = (unsigned char *) (workstr + len - 1); > > --- 3318,3350 ---- > * sort passes, etc. For now, we just shut down the whole thing in locales > * that do such things :-( > */ > ! Const * > ! make_greater_string(const Const *str_const) > { > + Oid datatype = str_const->consttype; > + char *str; > char *workstr; > int len; > > ! /* Get the string and a modifiable copy */ > ! if (datatype == NAMEOID) > ! { > ! str = DatumGetCString(DirectFunctionCall1(nameout, str_const->constvalue)); > ! len = strlen(str); > ! } > ! else if (datatype == BYTEAOID) > ! { > ! str = DatumGetCString(DirectFunctionCall1(byteaout, str_const->constvalue)); > ! len = toast_raw_datum_size(str_const->constvalue) - VARHDRSZ; > ! } > ! else > ! { > ! str = DatumGetCString(DirectFunctionCall1(textout, str_const->constvalue)); > ! len = strlen(str); > ! } > ! workstr = pstrdup(str); > > ! while (len > 0) > { > unsigned char *lastchar = (unsigned char *) (workstr + len - 1); > > *************** > *** 3243,3262 **** > { > (*lastchar)++; > if (string_lessthan(str, workstr, datatype)) > ! return workstr; /* Success! */ > } > > /* > * Truncate off the last character, which might be more than 1 > * byte in MULTIBYTE case. > */ > ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); > ! workstr[len] = '\0'; > } > > /* Failed... */ > pfree(workstr); > ! return NULL; > } > > /* > --- 3355,3388 ---- > { > (*lastchar)++; > if (string_lessthan(str, workstr, datatype)) > ! { > ! /* Success! */ > ! Const *workstr_const = string_to_const(workstr, datatype); > ! > ! pfree(str); > ! pfree(workstr); > ! return workstr_const; > ! } > } > > /* > * Truncate off the last character, which might be more than 1 > * byte in MULTIBYTE case. > */ > ! if (datatype != BYTEAOID && pg_database_encoding_max_length() > 1) > ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); > ! else > ! len -= - 1; > ! > ! if (datatype != BYTEAOID) > ! workstr[len] = '\0'; > } > > /* Failed... */ > + pfree(str); > pfree(workstr); > ! > ! return (Const *) NULL; > } > > /* > *************** > *** 3330,3341 **** > --- 3456,3471 ---- > static Datum > string_to_datum(const char *str, Oid datatype) > { > + Assert(str != NULL); > + > /* > * We cheat a little by assuming that textin() will do for bpchar and > * varchar constants too... > */ > if (datatype == NAMEOID) > return DirectFunctionCall1(namein, CStringGetDatum(str)); > + else if (datatype == BYTEAOID) > + return DirectFunctionCall1(byteain, CStringGetDatum(str)); > else > return DirectFunctionCall1(textin, CStringGetDatum(str)); > } > Index: src/include/catalog/pg_operator.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v > retrieving revision 1.107 > diff -c -r1.107 pg_operator.h > *** src/include/catalog/pg_operator.h 22 Aug 2002 04:45:11 -0000 1.107 > --- src/include/catalog/pg_operator.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 827,835 **** > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > --- 827,835 ---- > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > Index: src/include/catalog/pg_proc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v > retrieving revision 1.267 > diff -c -r1.267 pg_proc.h > *** src/include/catalog/pg_proc.h 1 Sep 2002 00:58:06 -0000 1.267 > --- src/include/catalog/pg_proc.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 2770,2782 **** > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > --- 2770,2782 ---- > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > Index: src/include/utils/selfuncs.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/selfuncs.h,v > retrieving revision 1.6 > diff -c -r1.6 selfuncs.h > *** src/include/utils/selfuncs.h 20 Jun 2002 20:29:53 -0000 1.6 > --- src/include/utils/selfuncs.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 33,44 **** > > /* selfuncs.c */ > > ! extern Pattern_Prefix_Status pattern_fixed_prefix(char *patt, > Pattern_Type ptype, > ! char **prefix, > ! char **rest); > extern bool locale_is_like_safe(void); > ! extern char *make_greater_string(const char *str, Oid datatype); > > extern Datum eqsel(PG_FUNCTION_ARGS); > extern Datum neqsel(PG_FUNCTION_ARGS); > --- 33,44 ---- > > /* selfuncs.c */ > > ! extern Pattern_Prefix_Status pattern_fixed_prefix(Const *patt, > Pattern_Type ptype, > ! Const **prefix, > ! Const **rest); > extern bool locale_is_like_safe(void); > ! extern Const *make_greater_string(const Const *str_const); > > extern Datum eqsel(PG_FUNCTION_ARGS); > extern Datum neqsel(PG_FUNCTION_ARGS); > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > Tom Lane wrote: > > Joe Conway <mail@joeconway.com> writes: > >>OK. I'll look at both options and make another diff -c proposal ;-) Once > >>that's resolved I'll go back to original issue Alvar raised. > > > > Okay. When you get back to the original issue, the gold is hidden in > > src/backend/optimizer/path/indxpath.c; see the "special indexable > > operators" stuff near the bottom of that file. (It's a bit of a crock > > that this code is hardwired there, and not somehow accessed through a > > system catalog, but it's what we've got at the moment.) > > The attached patch re-enables a bytea right hand argument (as compared > to a text right hand argument), and enables index usage, for bytea LIKE > -- e.g.: > > > parts=# explain select * from bombytea where parent_part like '05-05%'; > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using bombytea_idx1 on bombytea (cost=0.00..3479.67 > rows=1118 width=34) > Index Cond: ((parent_part >= '05-05'::bytea) AND (parent_part < > '05-06'::bytea)) > Filter: (parent_part ~~ '05-05%'::bytea) > (3 rows) > > > Passes all regression tests, and as far as I can tell does not break or > change the behavior of anything else. Please review and apply if there > are no objections (I'd like to see this applied for 7.3, before the > freeze, if possible, but I'll certainly understand if I'm told there's > not enough time left). > > Thanks, > > Joe > Index: src/backend/optimizer/path/indxpath.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/path/indxpath.c,v > retrieving revision 1.120 > diff -c -r1.120 indxpath.c > *** src/backend/optimizer/path/indxpath.c 13 Jul 2002 19:20:34 -0000 1.120 > --- src/backend/optimizer/path/indxpath.c 1 Sep 2002 22:19:16 -0000 > *************** > *** 97,103 **** > static bool match_special_index_operator(Expr *clause, Oid opclass, > bool indexkey_on_left); > static List *prefix_quals(Var *leftop, Oid expr_op, > ! char *prefix, Pattern_Prefix_Status pstatus); > static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); > static Oid find_operator(const char *opname, Oid datatype); > static Datum string_to_datum(const char *str, Oid datatype); > --- 97,103 ---- > static bool match_special_index_operator(Expr *clause, Oid opclass, > bool indexkey_on_left); > static List *prefix_quals(Var *leftop, Oid expr_op, > ! Const *prefix, Pattern_Prefix_Status pstatus); > static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop); > static Oid find_operator(const char *opname, Oid datatype); > static Datum string_to_datum(const char *str, Oid datatype); > *************** > *** 1675,1684 **** > Var *leftop, > *rightop; > Oid expr_op; > ! Datum constvalue; > ! char *patt; > ! char *prefix; > ! char *rest; > > /* > * Currently, all known special operators require the indexkey on the > --- 1675,1683 ---- > Var *leftop, > *rightop; > Oid expr_op; > ! Const *patt = NULL; > ! Const *prefix = NULL; > ! Const *rest = NULL; > > /* > * Currently, all known special operators require the indexkey on the > *************** > *** 1697,1703 **** > if (!IsA(rightop, Const) || > ((Const *) rightop)->constisnull) > return false; > ! constvalue = ((Const *) rightop)->constvalue; > > switch (expr_op) > { > --- 1696,1702 ---- > if (!IsA(rightop, Const) || > ((Const *) rightop)->constisnull) > return false; > ! patt = (Const *) rightop; > > switch (expr_op) > { > *************** > *** 1705,1772 **** > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest) != Pattern_Prefix_None; > ! if (prefix) > ! pfree(prefix); > ! pfree(patt); > ! } > break; > > case OID_TEXT_ICLIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_TEXT_REGEXEQ_OP: > case OID_BPCHAR_REGEXEQ_OP: > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_TEXT_ICREGEXEQ_OP: > case OID_BPCHAR_ICREGEXEQ_OP: > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > if (locale_is_like_safe()) > - { > - /* the right-hand const is type text for all of these */ > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest) != Pattern_Prefix_None; > - if (prefix) > - pfree(prefix); > - pfree(patt); > - } > break; > > case OID_INET_SUB_OP: > --- 1704,1748 ---- > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest) != Pattern_Prefix_None; > ! break; > ! > ! case OID_BYTEA_LIKE_OP: > ! isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, > ! &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_ICLIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_REGEXEQ_OP: > case OID_BPCHAR_REGEXEQ_OP: > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_TEXT_ICREGEXEQ_OP: > case OID_BPCHAR_ICREGEXEQ_OP: > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > + /* the right-hand const is type text for all of these */ > if (locale_is_like_safe()) > isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest) != Pattern_Prefix_None; > break; > > case OID_INET_SUB_OP: > *************** > *** 1777,1782 **** > --- 1753,1764 ---- > break; > } > > + if (prefix) > + { > + pfree(DatumGetPointer(prefix->constvalue)); > + pfree(prefix); > + } > + > /* done if the expression doesn't look indexable */ > if (!isIndexable) > return false; > *************** > *** 1798,1803 **** > --- 1780,1791 ---- > isIndexable = false; > break; > > + case OID_BYTEA_LIKE_OP: > + if (!op_in_opclass(find_operator(">=", BYTEAOID), opclass) || > + !op_in_opclass(find_operator("<", BYTEAOID), opclass)) > + isIndexable = false; > + break; > + > case OID_BPCHAR_LIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_BPCHAR_REGEXEQ_OP: > *************** > *** 1867,1876 **** > Var *leftop = get_leftop(clause); > Var *rightop = get_rightop(clause); > Oid expr_op = ((Oper *) clause->oper)->opno; > ! Datum constvalue; > ! char *patt; > ! char *prefix; > ! char *rest; > Pattern_Prefix_Status pstatus; > > switch (expr_op) > --- 1855,1863 ---- > Var *leftop = get_leftop(clause); > Var *rightop = get_rightop(clause); > Oid expr_op = ((Oper *) clause->oper)->opno; > ! Const *patt = (Const *) rightop; > ! Const *prefix = NULL; > ! Const *rest = NULL; > Pattern_Prefix_Status pstatus; > > switch (expr_op) > *************** > *** 1885,1902 **** > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > ! /* the right-hand const is type text for all of these */ > ! constvalue = ((Const *) rightop)->constvalue; > ! patt = DatumGetCString(DirectFunctionCall1(textout, > ! constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_ICLIKE_OP: > --- 1872,1883 ---- > case OID_BPCHAR_LIKE_OP: > case OID_VARCHAR_LIKE_OP: > case OID_NAME_LIKE_OP: > ! case OID_BYTEA_LIKE_OP: > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > break; > > case OID_TEXT_ICLIKE_OP: > *************** > *** 1904,1920 **** > case OID_VARCHAR_ICLIKE_OP: > case OID_NAME_ICLIKE_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_REGEXEQ_OP: > --- 1885,1895 ---- > *************** > *** 1922,1938 **** > case OID_VARCHAR_REGEXEQ_OP: > case OID_NAME_REGEXEQ_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_TEXT_ICREGEXEQ_OP: > --- 1897,1907 ---- > *************** > *** 1940,1966 **** > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > /* the right-hand const is type text for all of these */ > - constvalue = ((Const *) rightop)->constvalue; > - patt = DatumGetCString(DirectFunctionCall1(textout, > - constvalue)); > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > - if (prefix) > - pfree(prefix); > - pfree(patt); > break; > > case OID_INET_SUB_OP: > case OID_INET_SUBEQ_OP: > case OID_CIDR_SUB_OP: > case OID_CIDR_SUBEQ_OP: > - constvalue = ((Const *) rightop)->constvalue; > resultquals = nconc(resultquals, > network_prefix_quals(leftop, expr_op, > ! constvalue)); > break; > > default: > --- 1909,1928 ---- > case OID_VARCHAR_ICREGEXEQ_OP: > case OID_NAME_ICREGEXEQ_OP: > /* the right-hand const is type text for all of these */ > pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, > &prefix, &rest); > resultquals = nconc(resultquals, > prefix_quals(leftop, expr_op, > prefix, pstatus)); > break; > > case OID_INET_SUB_OP: > case OID_INET_SUBEQ_OP: > case OID_CIDR_SUB_OP: > case OID_CIDR_SUBEQ_OP: > resultquals = nconc(resultquals, > network_prefix_quals(leftop, expr_op, > ! patt->constvalue)); > break; > > default: > *************** > *** 1980,1994 **** > */ > static List * > prefix_quals(Var *leftop, Oid expr_op, > ! char *prefix, Pattern_Prefix_Status pstatus) > { > List *result; > Oid datatype; > Oid oproid; > Const *con; > Oper *op; > Expr *expr; > ! char *greaterstr; > > Assert(pstatus != Pattern_Prefix_None); > > --- 1942,1957 ---- > */ > static List * > prefix_quals(Var *leftop, Oid expr_op, > ! Const *prefix_const, Pattern_Prefix_Status pstatus) > { > List *result; > Oid datatype; > Oid oproid; > + char *prefix; > Const *con; > Oper *op; > Expr *expr; > ! Const *greaterstr = NULL; > > Assert(pstatus != Pattern_Prefix_None); > > *************** > *** 2001,2006 **** > --- 1964,1973 ---- > datatype = TEXTOID; > break; > > + case OID_BYTEA_LIKE_OP: > + datatype = BYTEAOID; > + break; > + > case OID_BPCHAR_LIKE_OP: > case OID_BPCHAR_ICLIKE_OP: > case OID_BPCHAR_REGEXEQ_OP: > *************** > *** 2027,2032 **** > --- 1994,2004 ---- > return NIL; > } > > + if (prefix_const->consttype != BYTEAOID) > + prefix = DatumGetCString(DirectFunctionCall1(textout, prefix_const->constvalue)); > + else > + prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefix_const->constvalue)); > + > /* > * If we found an exact-match pattern, generate an "=" indexqual. > */ > *************** > *** 2060,2076 **** > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(prefix, datatype); > if (greaterstr) > { > oproid = find_operator("<", datatype); > if (oproid == InvalidOid) > elog(ERROR, "prefix_quals: no < operator for type %u", datatype); > - con = string_to_const(greaterstr, datatype); > op = makeOper(oproid, InvalidOid, BOOLOID, false); > ! expr = make_opclause(op, leftop, (Var *) con); > result = lappend(result, expr); > - pfree(greaterstr); > } > > return result; > --- 2032,2046 ---- > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(con); > if (greaterstr) > { > oproid = find_operator("<", datatype); > if (oproid == InvalidOid) > elog(ERROR, "prefix_quals: no < operator for type %u", datatype); > op = makeOper(oproid, InvalidOid, BOOLOID, false); > ! expr = make_opclause(op, leftop, (Var *) greaterstr); > result = lappend(result, expr); > } > > return result; > *************** > *** 2186,2191 **** > --- 2156,2163 ---- > */ > if (datatype == NAMEOID) > return DirectFunctionCall1(namein, CStringGetDatum(str)); > + else if (datatype == BYTEAOID) > + return DirectFunctionCall1(byteain, CStringGetDatum(str)); > else > return DirectFunctionCall1(textin, CStringGetDatum(str)); > } > Index: src/backend/utils/adt/like.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v > retrieving revision 1.51 > diff -c -r1.51 like.c > *** src/backend/utils/adt/like.c 29 Aug 2002 07:22:26 -0000 1.51 > --- src/backend/utils/adt/like.c 1 Sep 2002 21:46:27 -0000 > *************** > *** 242,248 **** > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > --- 242,248 ---- > bytealike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > *************** > *** 263,269 **** > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! text *pat = PG_GETARG_TEXT_P(1); > bool result; > unsigned char *s, > *p; > --- 263,269 ---- > byteanlike(PG_FUNCTION_ARGS) > { > bytea *str = PG_GETARG_BYTEA_P(0); > ! bytea *pat = PG_GETARG_BYTEA_P(1); > bool result; > unsigned char *s, > *p; > Index: src/backend/utils/adt/selfuncs.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/selfuncs.c,v > retrieving revision 1.114 > diff -c -r1.114 selfuncs.c > *** src/backend/utils/adt/selfuncs.c 29 Aug 2002 07:22:27 -0000 1.114 > --- src/backend/utils/adt/selfuncs.c 1 Sep 2002 23:14:57 -0000 > *************** > *** 73,78 **** > --- 73,79 ---- > #include <locale.h> > > #include "access/heapam.h" > + #include "access/tuptoaster.h" > #include "catalog/catname.h" > #include "catalog/pg_namespace.h" > #include "catalog/pg_operator.h" > *************** > *** 168,175 **** > Var **var, Node **other, > bool *varonleft); > static void get_join_vars(List *args, Var **var1, Var **var2); > ! static Selectivity prefix_selectivity(Query *root, Var *var, char *prefix); > ! static Selectivity pattern_selectivity(char *patt, Pattern_Type ptype); > static bool string_lessthan(const char *str1, const char *str2, > Oid datatype); > static Oid find_operator(const char *opname, Oid datatype); > --- 169,176 ---- > Var **var, Node **other, > bool *varonleft); > static void get_join_vars(List *args, Var **var1, Var **var2); > ! static Selectivity prefix_selectivity(Query *root, Var *var, Const *prefix); > ! static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype); > static bool string_lessthan(const char *str1, const char *str2, > Oid datatype); > static Oid find_operator(const char *opname, Oid datatype); > *************** > *** 826,835 **** > bool varonleft; > Oid relid; > Datum constval; > - char *patt; > Pattern_Prefix_Status pstatus; > ! char *prefix; > ! char *rest; > double result; > > /* > --- 827,836 ---- > bool varonleft; > Oid relid; > Datum constval; > Pattern_Prefix_Status pstatus; > ! Const *patt = NULL; > ! Const *prefix = NULL; > ! Const *rest = NULL; > double result; > > /* > *************** > *** 853,863 **** > if (((Const *) other)->constisnull) > return 0.0; > constval = ((Const *) other)->constvalue; > ! /* the right-hand const is type text for all supported operators */ > ! Assert(((Const *) other)->consttype == TEXTOID); > ! patt = DatumGetCString(DirectFunctionCall1(textout, constval)); > > /* divide pattern into fixed prefix and remainder */ > pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); > > if (pstatus == Pattern_Prefix_Exact) > --- 854,866 ---- > if (((Const *) other)->constisnull) > return 0.0; > constval = ((Const *) other)->constvalue; > ! > ! /* the right-hand const is type text or bytea for all supported operators */ > ! Assert(((Const *) other)->consttype == TEXTOID || > ! ((Const *) other)->consttype == BYTEAOID); > > /* divide pattern into fixed prefix and remainder */ > + patt = (Const *) other; > pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); > > if (pstatus == Pattern_Prefix_Exact) > *************** > *** 866,879 **** > * Pattern specifies an exact match, so pretend operator is '=' > */ > Oid eqopr = find_operator("=", var->vartype); > - Const *eqcon; > List *eqargs; > > if (eqopr == InvalidOid) > elog(ERROR, "patternsel: no = operator for type %u", > var->vartype); > ! eqcon = string_to_const(prefix, var->vartype); > ! eqargs = makeList2(var, eqcon); > result = DatumGetFloat8(DirectFunctionCall4(eqsel, > PointerGetDatum(root), > ObjectIdGetDatum(eqopr), > --- 869,880 ---- > * Pattern specifies an exact match, so pretend operator is '=' > */ > Oid eqopr = find_operator("=", var->vartype); > List *eqargs; > > if (eqopr == InvalidOid) > elog(ERROR, "patternsel: no = operator for type %u", > var->vartype); > ! eqargs = makeList2(var, prefix); > result = DatumGetFloat8(DirectFunctionCall4(eqsel, > PointerGetDatum(root), > ObjectIdGetDatum(eqopr), > *************** > *** 903,910 **** > } > > if (prefix) > pfree(prefix); > ! pfree(patt); > > return result; > } > --- 904,913 ---- > } > > if (prefix) > + { > + pfree(DatumGetPointer(prefix->constvalue)); > pfree(prefix); > ! } > > return result; > } > *************** > *** 2693,2709 **** > */ > > static Pattern_Prefix_Status > ! like_fixed_prefix(char *patt, bool case_insensitive, > ! char **prefix, char **rest) > { > char *match; > int pos, > match_pos; > > ! *prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > ! for (pos = 0; patt[pos]; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%' || > --- 2696,2734 ---- > */ > > static Pattern_Prefix_Status > ! like_fixed_prefix(Const *patt_const, bool case_insensitive, > ! Const **prefix_const, Const **rest_const) > { > char *match; > + char *patt; > + int pattlen; > + char *prefix; > + char *rest; > + Oid typeid = patt_const->consttype; > int pos, > match_pos; > > ! /* the right-hand const is type text or bytea */ > ! Assert(typeid == BYTEAOID || typeid == TEXTOID); > ! > ! if (typeid == BYTEAOID && case_insensitive) > ! elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); > ! > ! if (typeid != BYTEAOID) > ! { > ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > ! pattlen = strlen(patt); > ! } > ! else > ! { > ! patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); > ! pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; > ! } > ! > ! prefix = match = palloc(pattlen + 1); > match_pos = 0; > > ! for (pos = 0; pos < pattlen; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%' || > *************** > *** 2713,2719 **** > if (patt[pos] == '\\') > { > pos++; > ! if (patt[pos] == '\0') > break; > } > > --- 2738,2744 ---- > if (patt[pos] == '\\') > { > pos++; > ! if (patt[pos] == '\0' && typeid != BYTEAOID) > break; > } > > *************** > *** 2733,2767 **** > } > > match[match_pos] = '\0'; > ! *rest = &patt[pos]; > > /* in LIKE, an empty pattern is an exact match! */ > ! if (patt[pos] == '\0') > return Pattern_Prefix_Exact; /* reached end of pattern, so > * exact */ > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > - pfree(match); > - *prefix = NULL; > return Pattern_Prefix_None; > } > > static Pattern_Prefix_Status > ! regex_fixed_prefix(char *patt, bool case_insensitive, > ! char **prefix, char **rest) > { > char *match; > int pos, > match_pos, > paren_depth; > > /* Pattern must be anchored left */ > if (patt[0] != '^') > { > ! *prefix = NULL; > ! *rest = patt; > return Pattern_Prefix_None; > } > > --- 2758,2815 ---- > } > > match[match_pos] = '\0'; > ! rest = &patt[pos]; > ! > ! *prefix_const = string_to_const(prefix, typeid); > ! *rest_const = string_to_const(rest, typeid); > ! > ! pfree(patt); > ! pfree(match); > ! prefix = NULL; > > /* in LIKE, an empty pattern is an exact match! */ > ! if (pos == pattlen) > return Pattern_Prefix_Exact; /* reached end of pattern, so > * exact */ > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > return Pattern_Prefix_None; > } > > static Pattern_Prefix_Status > ! regex_fixed_prefix(Const *patt_const, bool case_insensitive, > ! Const **prefix_const, Const **rest_const) > { > char *match; > int pos, > match_pos, > paren_depth; > + char *patt; > + char *prefix; > + char *rest; > + Oid typeid = patt_const->consttype; > + > + /* > + * Should be unnecessary, there are no bytea regex operators defined. > + * As such, it should be noted that the rest of this function has *not* > + * been made safe for binary (possibly NULL containing) strings. > + */ > + if (typeid == BYTEAOID) > + elog(ERROR, "Regex matching not supported on type BYTEA"); > + > + /* the right-hand const is type text for all of these */ > + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > > /* Pattern must be anchored left */ > if (patt[0] != '^') > { > ! rest = patt; > ! > ! *prefix_const = NULL; > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_None; > } > > *************** > *** 2774,2781 **** > { > if (patt[pos] == '|' && paren_depth == 0) > { > ! *prefix = NULL; > ! *rest = patt; > return Pattern_Prefix_None; > } > else if (patt[pos] == '(') > --- 2822,2832 ---- > { > if (patt[pos] == '|' && paren_depth == 0) > { > ! rest = patt; > ! > ! *prefix_const = NULL; > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_None; > } > else if (patt[pos] == '(') > *************** > *** 2792,2798 **** > } > > /* OK, allocate space for pattern */ > ! *prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > /* note start at pos 1 to skip leading ^ */ > --- 2843,2849 ---- > } > > /* OK, allocate space for pattern */ > ! prefix = match = palloc(strlen(patt) + 1); > match_pos = 0; > > /* note start at pos 1 to skip leading ^ */ > *************** > *** 2841,2865 **** > } > > match[match_pos] = '\0'; > ! *rest = &patt[pos]; > > if (patt[pos] == '$' && patt[pos + 1] == '\0') > { > ! *rest = &patt[pos + 1]; > return Pattern_Prefix_Exact; /* pattern specifies exact match */ > } > > if (match_pos > 0) > return Pattern_Prefix_Partial; > > - pfree(match); > - *prefix = NULL; > return Pattern_Prefix_None; > } > > Pattern_Prefix_Status > ! pattern_fixed_prefix(char *patt, Pattern_Type ptype, > ! char **prefix, char **rest) > { > Pattern_Prefix_Status result; > > --- 2892,2925 ---- > } > > match[match_pos] = '\0'; > ! rest = &patt[pos]; > > if (patt[pos] == '$' && patt[pos + 1] == '\0') > { > ! rest = &patt[pos + 1]; > ! > ! *prefix_const = string_to_const(prefix, typeid); > ! *rest_const = string_to_const(rest, typeid); > ! > return Pattern_Prefix_Exact; /* pattern specifies exact match */ > } > > + *prefix_const = string_to_const(prefix, typeid); > + *rest_const = string_to_const(rest, typeid); > + > + pfree(patt); > + pfree(match); > + prefix = NULL; > + > if (match_pos > 0) > return Pattern_Prefix_Partial; > > return Pattern_Prefix_None; > } > > Pattern_Prefix_Status > ! pattern_fixed_prefix(Const *patt, Pattern_Type ptype, > ! Const **prefix, Const **rest) > { > Pattern_Prefix_Status result; > > *************** > *** 2897,2915 **** > * more useful to use the upper-bound code than not. > */ > static Selectivity > ! prefix_selectivity(Query *root, Var *var, char *prefix) > { > Selectivity prefixsel; > Oid cmpopr; > ! Const *prefixcon; > List *cmpargs; > ! char *greaterstr; > > cmpopr = find_operator(">=", var->vartype); > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no >= operator for type %u", > var->vartype); > ! prefixcon = string_to_const(prefix, var->vartype); > cmpargs = makeList2(var, prefixcon); > /* Assume scalargtsel is appropriate for all supported types */ > prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, > --- 2957,2979 ---- > * more useful to use the upper-bound code than not. > */ > static Selectivity > ! prefix_selectivity(Query *root, Var *var, Const *prefixcon) > { > Selectivity prefixsel; > Oid cmpopr; > ! char *prefix; > List *cmpargs; > ! Const *greaterstrcon; > > cmpopr = find_operator(">=", var->vartype); > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no >= operator for type %u", > var->vartype); > ! if (prefixcon->consttype != BYTEAOID) > ! prefix = DatumGetCString(DirectFunctionCall1(textout, prefixcon->constvalue)); > ! else > ! prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefixcon->constvalue)); > ! > cmpargs = makeList2(var, prefixcon); > /* Assume scalargtsel is appropriate for all supported types */ > prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, > *************** > *** 2923,2930 **** > * "x < greaterstr". > *------- > */ > ! greaterstr = make_greater_string(prefix, var->vartype); > ! if (greaterstr) > { > Selectivity topsel; > > --- 2987,2994 ---- > * "x < greaterstr". > *------- > */ > ! greaterstrcon = make_greater_string(prefixcon); > ! if (greaterstrcon) > { > Selectivity topsel; > > *************** > *** 2932,2939 **** > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no < operator for type %u", > var->vartype); > ! prefixcon = string_to_const(greaterstr, var->vartype); > ! cmpargs = makeList2(var, prefixcon); > /* Assume scalarltsel is appropriate for all supported types */ > topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, > PointerGetDatum(root), > --- 2996,3002 ---- > if (cmpopr == InvalidOid) > elog(ERROR, "prefix_selectivity: no < operator for type %u", > var->vartype); > ! cmpargs = makeList2(var, greaterstrcon); > /* Assume scalarltsel is appropriate for all supported types */ > topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, > PointerGetDatum(root), > *************** > *** 2997,3010 **** > #define PARTIAL_WILDCARD_SEL 2.0 > > static Selectivity > ! like_selectivity(char *patt, bool case_insensitive) > { > Selectivity sel = 1.0; > int pos; > > /* Skip any leading %; it's already factored into initial sel */ > ! pos = (*patt == '%') ? 1 : 0; > ! for (; patt[pos]; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%') > --- 3060,3094 ---- > #define PARTIAL_WILDCARD_SEL 2.0 > > static Selectivity > ! like_selectivity(Const *patt_const, bool case_insensitive) > { > Selectivity sel = 1.0; > int pos; > + int start; > + Oid typeid = patt_const->consttype; > + char *patt; > + int pattlen; > + > + /* the right-hand const is type text or bytea */ > + Assert(typeid == BYTEAOID || typeid == TEXTOID); > + > + if (typeid == BYTEAOID && case_insensitive) > + elog(ERROR, "Cannot perform case insensitive matching on type BYTEA"); > + > + if (typeid != BYTEAOID) > + { > + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > + pattlen = strlen(patt); > + } > + else > + { > + patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue)); > + pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; > + } > > /* Skip any leading %; it's already factored into initial sel */ > ! start = (*patt == '%') ? 1 : 0; > ! for (pos = start; pos < pattlen; pos++) > { > /* % and _ are wildcard characters in LIKE */ > if (patt[pos] == '%') > *************** > *** 3015,3021 **** > { > /* Backslash quotes the next character */ > pos++; > ! if (patt[pos] == '\0') > break; > sel *= FIXED_CHAR_SEL; > } > --- 3099,3105 ---- > { > /* Backslash quotes the next character */ > pos++; > ! if (patt[pos] == '\0' && typeid != BYTEAOID) > break; > sel *= FIXED_CHAR_SEL; > } > *************** > *** 3122,3131 **** > } > > static Selectivity > ! regex_selectivity(char *patt, bool case_insensitive) > { > Selectivity sel; > ! int pattlen = strlen(patt); > > /* If patt doesn't end with $, consider it to have a trailing wildcard */ > if (pattlen > 0 && patt[pattlen - 1] == '$' && > --- 3206,3229 ---- > } > > static Selectivity > ! regex_selectivity(Const *patt_const, bool case_insensitive) > { > Selectivity sel; > ! char *patt; > ! int pattlen; > ! Oid typeid = patt_const->consttype; > ! > ! /* > ! * Should be unnecessary, there are no bytea regex operators defined. > ! * As such, it should be noted that the rest of this function has *not* > ! * been made safe for binary (possibly NULL containing) strings. > ! */ > ! if (typeid == BYTEAOID) > ! elog(ERROR, "Regex matching not supported on type BYTEA"); > ! > ! /* the right-hand const is type text for all of these */ > ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue)); > ! pattlen = strlen(patt); > > /* If patt doesn't end with $, consider it to have a trailing wildcard */ > if (pattlen > 0 && patt[pattlen - 1] == '$' && > *************** > *** 3146,3152 **** > } > > static Selectivity > ! pattern_selectivity(char *patt, Pattern_Type ptype) > { > Selectivity result; > > --- 3244,3250 ---- > } > > static Selectivity > ! pattern_selectivity(Const *patt, Pattern_Type ptype) > { > Selectivity result; > > *************** > *** 3220,3238 **** > * sort passes, etc. For now, we just shut down the whole thing in locales > * that do such things :-( > */ > ! char * > ! make_greater_string(const char *str, Oid datatype) > { > char *workstr; > int len; > > ! /* > ! * Make a modifiable copy, which will be our return value if > ! * successful > ! */ > ! workstr = pstrdup((char *) str); > > ! while ((len = strlen(workstr)) > 0) > { > unsigned char *lastchar = (unsigned char *) (workstr + len - 1); > > --- 3318,3350 ---- > * sort passes, etc. For now, we just shut down the whole thing in locales > * that do such things :-( > */ > ! Const * > ! make_greater_string(const Const *str_const) > { > + Oid datatype = str_const->consttype; > + char *str; > char *workstr; > int len; > > ! /* Get the string and a modifiable copy */ > ! if (datatype == NAMEOID) > ! { > ! str = DatumGetCString(DirectFunctionCall1(nameout, str_const->constvalue)); > ! len = strlen(str); > ! } > ! else if (datatype == BYTEAOID) > ! { > ! str = DatumGetCString(DirectFunctionCall1(byteaout, str_const->constvalue)); > ! len = toast_raw_datum_size(str_const->constvalue) - VARHDRSZ; > ! } > ! else > ! { > ! str = DatumGetCString(DirectFunctionCall1(textout, str_const->constvalue)); > ! len = strlen(str); > ! } > ! workstr = pstrdup(str); > > ! while (len > 0) > { > unsigned char *lastchar = (unsigned char *) (workstr + len - 1); > > *************** > *** 3243,3262 **** > { > (*lastchar)++; > if (string_lessthan(str, workstr, datatype)) > ! return workstr; /* Success! */ > } > > /* > * Truncate off the last character, which might be more than 1 > * byte in MULTIBYTE case. > */ > ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); > ! workstr[len] = '\0'; > } > > /* Failed... */ > pfree(workstr); > ! return NULL; > } > > /* > --- 3355,3388 ---- > { > (*lastchar)++; > if (string_lessthan(str, workstr, datatype)) > ! { > ! /* Success! */ > ! Const *workstr_const = string_to_const(workstr, datatype); > ! > ! pfree(str); > ! pfree(workstr); > ! return workstr_const; > ! } > } > > /* > * Truncate off the last character, which might be more than 1 > * byte in MULTIBYTE case. > */ > ! if (datatype != BYTEAOID && pg_database_encoding_max_length() > 1) > ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1); > ! else > ! len -= - 1; > ! > ! if (datatype != BYTEAOID) > ! workstr[len] = '\0'; > } > > /* Failed... */ > + pfree(str); > pfree(workstr); > ! > ! return (Const *) NULL; > } > > /* > *************** > *** 3330,3341 **** > --- 3456,3471 ---- > static Datum > string_to_datum(const char *str, Oid datatype) > { > + Assert(str != NULL); > + > /* > * We cheat a little by assuming that textin() will do for bpchar and > * varchar constants too... > */ > if (datatype == NAMEOID) > return DirectFunctionCall1(namein, CStringGetDatum(str)); > + else if (datatype == BYTEAOID) > + return DirectFunctionCall1(byteain, CStringGetDatum(str)); > else > return DirectFunctionCall1(textin, CStringGetDatum(str)); > } > Index: src/include/catalog/pg_operator.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v > retrieving revision 1.107 > diff -c -r1.107 pg_operator.h > *** src/include/catalog/pg_operator.h 22 Aug 2002 04:45:11 -0000 1.107 > --- src/include/catalog/pg_operator.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 827,835 **** > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > --- 827,835 ---- > DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel)); > DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel)); > DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel)); > ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel)); > #define OID_BYTEA_LIKE_OP 2016 > ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel)); > DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - )); > > /* timestamp operators */ > Index: src/include/catalog/pg_proc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v > retrieving revision 1.267 > diff -c -r1.267 pg_proc.h > *** src/include/catalog/pg_proc.h 1 Sep 2002 00:58:06 -0000 1.267 > --- src/include/catalog/pg_proc.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 2770,2782 **** > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > --- 2770,2782 ---- > DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); > DESCR("adjust time with time zone precision"); > > ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ )); > DESCR("matches LIKE expression"); > ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ )); > DESCR("does not match LIKE expression"); > DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ )); > DESCR("convert match pattern to use backslash escapes"); > Index: src/include/utils/selfuncs.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/selfuncs.h,v > retrieving revision 1.6 > diff -c -r1.6 selfuncs.h > *** src/include/utils/selfuncs.h 20 Jun 2002 20:29:53 -0000 1.6 > --- src/include/utils/selfuncs.h 1 Sep 2002 21:46:27 -0000 > *************** > *** 33,44 **** > > /* selfuncs.c */ > > ! extern Pattern_Prefix_Status pattern_fixed_prefix(char *patt, > Pattern_Type ptype, > ! char **prefix, > ! char **rest); > extern bool locale_is_like_safe(void); > ! extern char *make_greater_string(const char *str, Oid datatype); > > extern Datum eqsel(PG_FUNCTION_ARGS); > extern Datum neqsel(PG_FUNCTION_ARGS); > --- 33,44 ---- > > /* selfuncs.c */ > > ! extern Pattern_Prefix_Status pattern_fixed_prefix(Const *patt, > Pattern_Type ptype, > ! Const **prefix, > ! Const **rest); > extern bool locale_is_like_safe(void); > ! extern Const *make_greater_string(const Const *str_const); > > extern Datum eqsel(PG_FUNCTION_ARGS); > extern Datum neqsel(PG_FUNCTION_ARGS); > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi, -- Joe Conway <mail@joeconway.com> wrote: > The attached patch re-enables a bytea right hand argument (as compared > to a text right hand argument), and enables index usage, for bytea LIKE > -- e.g.: > > > parts=# explain select * from bombytea where parent_part like '05-05%'; With this, the limits in Bytea indexes are removed? Great! As far is I followed the discussion, there where some additionsl limits on bytea indexes (and like); or are they removed with this patch? I'll try it sometime later and make some performance checks -- in the hopem that bytea is faster then texts ;-) (at least the base 255 encoding i can throw out of my application). Thanks && Ciao Alvar -- ** Alvar C.H. Freude ** http://alvar.a-blast.org/ ** ** http://odem.org/