Casting issues with Postgres Backend (columns not declared as type int)

[steven.studniarz@…]

When trying to set build configuration parameters, I'm getting the following errors (with a Postgres database):

Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/web/main.py", line 435, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/web/main.py", line 205, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/admin/web_ui.py", line 113, in process_request
    path_info)
  File "build/bdist.linux-x86_64/egg/bitten/admin.py", line 145, in render_admin_panel
    config=config.name))
  File "build/bdist.linux-x86_64/egg/bitten/model.py", line 320, in select
    yield TargetPlatform.fetch(env, id)
  File "build/bdist.linux-x86_64/egg/bitten/model.py", line 294, in fetch
    "WHERE id=%s ORDER BY orderno", (id,))
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/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 propname,pattern FROM bitten_rule WHERE id=1 ORDER BY...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

The issue seems to be with not (single) quoting the value in the WHERE check. I have a feeling issues like this may manifest them elsewhere, as well.

[dfraser]

Could you try and run that SQL statement from the postgresql command line and work out what it should be? i.e. get it to a version that runs successfully...

[dfraser]

Also, was this really bitten 0.5.3, or did you just leave the default version number in? :-)

If it's not, could you say what svn branch and revision it is?

[steven.studniarz@…]

The query actually works with single quotes around the value ( id='1' instead of id=1 ). It appears that for some reason the table in my Postgres DB (bitten_rule) has that field as being a text field. Perhaps this is a bug in the table creation functionality?

This is against r626 of the trunk (0.6 dev) - updating the ticket to reflect that.

[steven.studniarz@…]

I just confirmed that this behavior still exists in r629 , also...

[dfraser]

There are a few problems here:

• The id column for bitten_rule should be declared as having type int
• The id column for bitten_rule could possibly be renamed to platform as it's not a unique id for this table but rather a reference to bitten_platform.id
• There are lots of other columns that could be declared as type int (e.g. bitten_config.max_rev)
• We should have unit tests running on Postgres, which would catch all these problems

Changing the column types is going to be tricky though - we'll probably have to add schema upgrades that will work on Postgres and migrate the data. For this reason it would make sense to do all the changes in one go.

[dfraser]

Replying to dfraser:

• There are lots of other columns that could be declared as type int (e.g. bitten_config.max_rev)

rev columns should actually not be ints, as Simon Cross pointed out in the mailing list:

Probably not. Many of the distributed version control systems have hash strings as revision ids.

Although this does raise the question of what max_rev means in that context, which I'm currently ignoring.

See the more general discussion at ​Adding target leads to Python exception

[dfraser]

wbell committed r639 to fix this, including schema upgrades - steven.studniarz can you test and confirm?

[anonymous]

It seems to have gotten past the id casting issue, but now we seem to have the same issue with the rev:

2009-05-18 08:25:45,701 Trac[main] ERROR: Internal Server Error:
Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/web/main.py", line 435, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/web/main.py", line 205, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-x86_64/egg/bitten/web_ui.py", line 156, in process_request
    data = self._render_overview(req)
  File "build/bdist.linux-x86_64/egg/bitten/web_ui.py", line 218, in _render_overview
    for platform, rev, build in collect_changes(repos, config):
  File "build/bdist.linux-x86_64/egg/bitten/queue.py", line 84, in collect_changes
    db=db))
  File "build/bdist.linux-x86_64/egg/bitten/model.py", line 518, in select
    % where, [wc[1] for wc in where_clauses])
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.4-py2.5.egg/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: ...T id FROM bitten_build WHERE config=E'CSS' AND rev=4657 AND ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

[jhampton]

Replying to anonymous:

It seems to have gotten past the id casting issue, but now we seem to have the same issue with the rev:

I'm using PostgreSQL 8.3 and the current trunk and have not had any issues. For a look at the code, these issues should have been fixed. If you upgrade, are you still having issues?

[jhampton]

FYI, this should have been resolved in [640] and [641]. I'm going to close the ticket as worksforme in a few days if I don't hear anything to the contrary

[osimons]

No response. I'm closing.