Thread: 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/