PostgreSQL ERROR: operator does not exist: text = integer

[matthew.wensing@…]

Created a fresh trac environment, pointed it to an existing SVN repos, and clicked 'Browse Source'. Returned the error:

Trac detected an internal error:

ProgrammingError: operator does not exist: text = integer
LINE 1: SELECT time,author,message FROM revision WHERE rev=1
                                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

This query works fine:

SELECT time,author,message FROM revision WHERE rev='1'

trac 0.11dev-r6120 svn 1.4.0 psql 8.3beta1 Python 2.5

[anonymous]

This is introduced with 8.3beta1.

[Christian Boos]

Looks like this uncovers a non-consistent usage of the rev. It should really be handled as a string at the db level, but apparently no other backend/binding complained so far when given an int…

What bindings are you using? If you're not using psycopg2 already, can you try using those?

[anonymous]

psycopg2

And I've been told by some folks in #postgres that this will remain the case for 8.3.

[anonymous]

Probably goes without saying, but either quoting '1' or using text_data::int = 1 should do the trick.

[Christian Boos]

Well, the right fix is a bit more involved, as currently the svn_fs backend normalizes revision as ints, but the cache layer wants to see a text identifier for the rev, useful for backends that have hexa hash numbers as revision ids (but then, I'm not sure if there's any such backend that uses the cache in its current form).

As a quick workaround, if you're not using a big repository, you can simply disable caching:

[trac]
repository_type = direct-svnfs

[anonymous]

Excellent. Many thanks.

[anonymous]

Just noting that the workaround you suggest works.

[anonymous]

Also getting this. Any workarounds for this one? This comes up when I try to submit a ticket.

ProgrammingError: operator does not exist: text = integer
LINE 1: ...mment',null,description,0 FROM attachment WHERE id=1 ORDER B...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

[anonymous]

For now I just edited ticket/model.py to wrap id=%s in quotes, id='%s'.

[Christian Boos]

From #6670:

"WHERE rev=%s", (str(rev),)) works perfectly. This is in Postgres 8.3beta4.

I think the str() case is the correct thing to do here.

[mgohlke@…]

Why not just wrap it in str(rev) as it is in other parts of versioncontrol/cache.py:CachedChangeset. That's the only place in the file that it's not stringified.

[anonymous]

Replying to mgohlke@handmark.com:

Why not just wrap it in str(rev) as it is in other parts of versioncontrol/cache.py:CachedChangeset. That's the only place in the file that it's not stringified.

Ignore. Just saw the comment above mentioning the str() cast.

[Christian Boos]

He he, yes.

Should be fixed in r6415 for trunk and r6416 for 0.10.5dev.