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);