[Pkg-trac-devel] Bug#464168: trac: Does not work with PostgreSQL 8.3 - column type conflict

[Tomasz Rybak]

Package: trac
Version: 0.10.4-2
Severity: serious

Today I upgraded PostgreSQL to 8.3.
This version more strictly checks types of columns and parameters.
After this upgrade any operations in Trac that try to access Subversion
repository (Timeline, Browse Source) fails.
For example "Browse source" results in following web page:

Python Traceback

Traceback (most recent call last):
File "/var/lib/python-support/python2.4/trac/web/main.py", line 406, in dispatch_request
	dispatcher.dispatch(req)
File "/var/lib/python-support/python2.4/trac/web/main.py", line 237, in dispatch
	resp = chosen_handler.process_request(req)
File "/var/lib/python-support/python2.4/trac/versioncontrol/web_ui/browser.py", line 143, in process_request
	self._render_directory(req, repos, node, rev)
File "/var/lib/python-support/python2.4/trac/versioncontrol/web_ui/browser.py", line 168, in _render_directory
	changes = get_changes(self.env, repos, [i['rev'] for i in info])
File "/var/lib/python-support/python2.4/trac/versioncontrol/web_ui/util.py", line 37, in get_changes
	changeset = repos.get_changeset(rev)
File "/var/lib/python-support/python2.4/trac/versioncontrol/cache.py", line 45, in get_changeset
self.authz)
File "/var/lib/python-support/python2.4/trac/versioncontrol/cache.py", line 247, in __init__
	"WHERE rev=%s", (rev,))
File "/var/lib/python-support/python2.4/trac/db/util.py", line 50, in execute
	return self.cursor.execute(sql_escape_percent(sql), args)
File "/var/lib/python-support/python2.4/trac/db/util.py", line 50, in execute
	return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError:
operator does not exist: text = integer
LINE 1: SELECT time,author,message FROM revision WHERE rev=238
                                                          ^
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.


Problem lies in table revision, column rev; this column has type text, 
which conflicts with number given to query as parameter.
Changing type of this column to integer helps.
It looks like there is no other column referencing revisions.rev,
so this can be done without problem.

Following SQL commands allow for fixing exisitng Trac database;
after those, Trac works again.

alter table revision add column r integer;
update revision set r = rev::int;
alter table revision drop column rev;
alter table revision rename column r to rev;
alter table revision alter column rev set not null;
alter table revision add primary key (rev);


I set severity for serious because this error makes Trac only partially
usable, although it does not cause data loss.

-- System Information:
Debian Release: lenny/sid
APT prefers unstable
APT policy: (500, 'unstable'), (1, 'experimental')
Architecture: i386 (i686)

Kernel: Linux 2.6.23
Locale: LANG=polish, LC_CTYPE=pl_PL.UTF-8 (charmap=UTF-8) (ignored: LC_ALL set to pl_PL.UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages trac depends on:
ii  python                      2.4.4-6      An interactive high-level object-o
ii  python-clearsilver          0.10.4-1     python bindings for clearsilver
ii  python-pysqlite2            2.4.0-2      python interface to SQLite 3
ii  python-subversion           1.4.4dfsg1-1 Python bindings for Subversion
ii  python-support              0.7.6        automated rebuilding support for p
ii  subversion                  1.4.4dfsg1-1 Advanced version control system

Versions of packages trac recommends:
ii  apache2                       2.2.8-1    Next generation, scalable, extenda
ii  apache2-mpm-worker [httpd]    2.2.8-1    High speed threaded model for Apac
ii  python-setuptools             0.6c7-1    Python Distutils Enhancements

-- debconf-show failed