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