Viewing a ticket after patch installed leads to backtrace

[grib@…]

I patched my r6000 Trac source with the BackRefPatch svn r2507. Upon viewing a ticket:

02:29:03 PM Trac[main] ERROR: ERROR:  UNION types integer and text cannot be matched
Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/web/main.py", line 381, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/web/main.py", line 191, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/ticket/web_ui.py", line 145, in process_request
    return self._process_ticket_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/ticket/web_ui.py", line 442, in _process_ticket_request
    data['backrefs'] = self._get_backrefs(context, ticket)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/ticket/web_ui.py", line 928, in _get_backrefs
    % ((id,) * 8))
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r6000-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.5/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
    raise OperationalError, msg
OperationalError: ERROR:  UNION types integer and text cannot be matched

As you can see this is with a postgresql database.

From looking at the source, the UNIONs at source lines 902-921 of ticket/web_ui.py and also 916-939 of versioncontrol/web_ui/changeset.py, 545-560 of wiki/web_ui.py are the problem. wiki.name is a text field, ticket.id is an int. Let me guess: you developed this using SQLite, the Perl of SQL databases (at least as far as typing goes) :)

I believe that just casting the integers to a text type will work, but I'm not sure how to do this in a database-agnostic way.

[grib@…]

Replying to grib@billgribble.com:

I believe that just casting the integers to a text type will work, but I'm not sure how to do this in a database-agnostic way.

To followup myself, this does work. But it brings up another problem in my Trac instance of about 3500 tickets and 2500 wiki pages... sloooooow for big, highly crossreffed wikis! Oh well. I wonder if there's a way to make the backrefs section just a clickable that opens up another page on demand...

[Norbert Ferchen]

This is the point where one thing leads to another. With the first version this patch only crawls tickets and changesets for links to tickets. Now we got a lot more. The query will allways be a sequencial scan on the db, and sqlite is very fast on that. I've tried to migrate to mssql an stuck on unions with simular reasons, which I could not solve easily. But the performance was terrible slow, so I quit the intension with mssql.

There a several possible solutions: First is to avoid sequencial scans an build a own table with references, feeded within saving new content. But after that, this would not be a easy way. Second is to strip the sql-statement to your needs and perhaps avoid scanning of wiki-pages. And after all: you should give sqlite a try: I "only" got 1.500 Tickets an no performance impact.

[anonymous]

I think for most Trac instances your approach probably works fine, however mine is a little special :) The whole development and customer support effort runs off of it, and the wiki is very highly crossreferenced. We also have other tools that crawl through and interact with the trac database over the network, so sqlite isn't really an option.

Anyway, I started looking at your patch because I'm used to wikis where you can click on the title of each wiki page and get a new page with the backreferences listed. You might just consider this a feature request if you continue working on this patch: instead of a listing of the backreferences at the bottom of the page, an option to have just a link to click to fetch them. That way normal page loads don't crawl the DB, and when you want the crossrefs you know it might take a second or two.

Thanks for your patch!

[Ryan J Ollos]

Patch is deprecated.