Edgewall Software

Opened 14 years ago

Closed 14 years ago

Last modified 7 years ago

#6929 closed defect (fixed)

Search broken on Postgresql 8.3

Reported by: pjenvey@… Owned by: Jonas Borgström
Priority: normal Milestone: 0.11
Component: search system Version: devel
Severity: major Keywords: search, postgresql, 8.3
Cc: nulleke76@… Branch:
Release Notes:
API Changes:
Internal Changes:


Search needs more of the same explicit type casting that #6676 now does

Under trunk r6633, searching yields:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/api.py", line 339, in send_error
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/chrome.py", line 690, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/api.py", line 168, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/main.py", line 256, in _get_session
    return Session(self.env, req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/session.py", line 48, in __init__
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/session.py", line 74, in get_session
    (sid, int(authenticated)))
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

Postgresql says (a search for 'registry'):

2008-03-03 15:58:11 PST ERROR:  operator does not exist: integer ~~* text at character 440
2008-03-03 15:58:11 PST HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2008-03-03 15:58:11 PST STATEMENT:  SELECT DISTINCT a.summary,a.description,a.reporter, 
a.type,a.id,a.time,a.status,a.resolution FROM ticket a LEFT JOIN ticket_change b ON a.id = b.ticket WHERE (b.field='comment' 
AND (b.newvalue ILIKE '%registry%' ESCAPE '/') ) OR (summary ILIKE '%registry%' ESCAPE '/' OR keywords ILIKE '%registry%' 
ESCAPE '/' OR description ILIKE '%registry%' ESCAPE '/' OR reporter ILIKE '%registry%' ESCAPE '/' OR cc ILIKE '%registry%' ESCAPE 
'/' OR id ILIKE '%registry%' ESCAPE '/')

So the search query is treating the id column as text when it's an integer. We need to change

id ILIKE '%registry%' ESCAPE '/'


text(id) ILIKE '%registry%' ESCAPE '/'

Attachments (0)

Change History (4)

comment:1 by anonymous, 14 years ago

Cc: nulleke76@… added

comment:2 by Jonas Borgström, 14 years ago

Milestone: 0.11
Status: newassigned

comment:3 by Jonas Borgström, 14 years ago

Resolution: fixed
Status: assignedclosed

Fixed [6652].

comment:4 by Ryan J Ollos, 7 years ago

Keywords: search postgresql 8.3 → search, postgresql, 8.3

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Jonas Borgström.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jonas Borgström to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.