installation breaks creation of reports

[tekknokrat]

I am using the recent version of Trac (0.11-stable_7092) and TimingEstimation Plugin with postgresql backend.

When I activate the timingandestimation plugin and try to customize a ticket report I get this error:

2008-06-03 18:02:03,980 Trac[main] ERROR: duplicate key value violates unique constraint "report_pkey"
Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r7182-py2.5.egg/trac/web/main.py", line 423, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r7182-py2.5.egg/trac/web/main.py", line 197, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r7182-py2.5.egg/trac/ticket/report.py", line 93, in process_request
    self._do_create(req, db)
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r7182-py2.5.egg/trac/ticket/report.py", line 141, in _do_create
    "VALUES (%s,%s,%s)", (title, query, description))
  File "/usr/lib/python2.5/site-packages/Trac-0.11dev_r7182-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.11dev_r7182-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: duplicate key value violates unique constraint "report_pkey"

I tried with 1 sqlite based environment where this error does not occur. Workaround I first had was to manually removing the t&e reports from database. After that creating/modifying of the reports worked. But when trying to restore the reports through trac-admin upgrade it seems this gets broken because of manual deleting reports.

[anebi]

I get the same error when i try to create reports. We use postgresql 8.1. I have installed trac 0.11 stable version. I get this error: Traceback (most recent call last):

File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 423, in _dispatch_request

dispatcher.dispatch(req)

File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 197, in dispatch

resp = chosen_handler.process_request(req)

File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/ticket/report.py", line 93, in process_request

self._do_create(req, db)

File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/ticket/report.py", line 141, in _do_create

"VALUES (%s,%s,%s)", (title, query, description))

File "/usr/lib/python2.4/site-packages/Trac-0.11-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.11-py2.4.egg/trac/db/util.py", line 50, in execute

return self.cursor.execute(sql_escape_percent(sql), args)

IntegrityError: duplicate key violates unique constraint "report_pkey"

[Russ Tyndall]

Hmmm... All of theses errors arise from trac and not this plugin (per the stack trace which never mentions this plugin). It seems that there might be a bug in trac 11 (and apparently 9 and 10) about adding reports to postegres. My best guess is that the report table should be changed to have a serial type for the primary key instead of integer with default sequence calls. (Though they should be functionally equivalent, they are obviously not acting that way).

This would cause it to grab a new id rather than inserting null. When run against sqlite, this is the default behavior.

Please see the trac bug for more info: ​http://trac.edgewall.org/ticket/3948

If you still think this is my problem, feel free to reopen.

[Russ Tyndall]

One more point to note, is that the TimingAndEstimationPlugin does not rely on that column filling in a correct value. Instead it queries for the highest id report and manually provides the next highest when it inserts into the table. This could cause problems for the sequence as we are not relying on it actually incrementing. I think that we did that so that it would work in MySql (though I was not the originator of the current reportmanager code, so I am not sure).

What I can try to do is add a call that will increment that counter in a try/catch block after the insert. This would probably allow the counter to be incremented where it needed to be and not on databases that do not support this.

[Russ Tyndall]

(In [4020]) closes #3125 Added some code to make the postgres install not choke after inserting reports. This basically involved manually moving the sequence forward.