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 ....
Actually, most search operataions using the attachment data seem to fail.
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.)
Severity: major → minor
Component: Query/Bug List → Database
Summary: Postgres throws exception for "contains the string" case insensitive search → [PostgreSQL] Boolean Chart searches on attachment data don't work
mkanat, couldn't we cast the attachment data to text when using lower()?
(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.
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.
Target Milestone: --- → Bugzilla 4.2
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.
Created attachment 456487 [details] [diff] [review] v1 This updates our requirement to PostgreSQL 8.3.
Assignee: database → mkanat
Status: NEW → ASSIGNED
Attachment #456487 - Flags: review+
Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/ modified Bugzilla/Constants.pm Committed revision 7306.
Status: ASSIGNED → RESOLVED
Last Resolved: 8 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.