305412 - [PostgreSQL] Boolean Chart searches on attachment data don't work

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Joel Peshkin]

Looks like PG doesn't like lower for attachment data fields

DBD::Pg::st execute failed: ERROR:  function lower(bytea) does not exist [for
Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity,
bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status,
bugs.resolution, bugs.short_desc FROM bugs  INNER JOIN profiles AS
map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN
products AS ....

Comment 1

[Joel Peshkin]

Actually, most search operataions using the attachment data seem to fail.

Comment 2

[Max Kanat-Alexander]

That makes sense. I think it's only minor, though, since PostgreSQL support is
technically still "experimental" at this point and searching attachment data
isn't an extremely common operation.

In particular, case-insensitive searching of an attachment will probably not
actually be possible in PostgreSQL. (As it says, there is no LOWER(bytea) function.)

Comment 3

[Frédéric Buclin]

mkanat, couldn't we cast the attachment data to text when using lower()?

Comment 4

[Max Kanat-Alexander]

(In reply to comment #3)
> mkanat, couldn't we cast the attachment data to text when using lower()?

  No, you can't cast a BLOB:

bugs_mkanat=# SELECT thedata::text FROM attach_data LIMIT 1;
ERROR:  cannot cast type bytea to text

  Good thought, though.

Comment 5

[Frédéric Buclin]

This would be a hack, but maybe could we redirect 'substring' to 'casesubstring' in this very special case (and for Pg only). Note that 'notsubstring' would need to be fixed as well.

Comment 6

[Max Kanat-Alexander]

Okay, the simple solution is to require Pg 8.3, which does support this. I believe that 8.3 will also get us built-in fulltext searching, if we want to support that.

Comment 7

[Max Kanat-Alexander]

Attachment: v1

This updates our requirement to PostgreSQL 8.3.

Comment 8

[Max Kanat-Alexander]

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/Constants.pm
Committed revision 7306.