Unable to browse source. Fails with "duplicate key violates unique constraint 'revision_pkey'

[dhaval.shah.m@…]

I am using trac 0.10.

We are looking to integrate it with Perforce and Postgres. The perforce change list is very large.

Postgres v 8.1.4, Perforce v 2.5...

When I do "browse source", I get the following error :

Traceback (most recent call last):
  File "/home/trac/lib/python2.4/site-packages/trac/web/main.py", line 356, in dispatch_request
    dispatcher.dispatch(req)
  File "/home/trac/lib/python2.4/site-packages/trac/web/main.py", line 224, in dispatch
    resp = chosen_handler.process_request(req)
  File "/home/trac/lib/python2.4/site-packages/trac/versioncontrol/web_ui/browser.py", line 131, in process_request
    self._render_directory(req, repos, node, rev)
  File "/home/trac/lib/python2.4/site-packages/trac/versioncontrol/web_ui/browser.py", line 156, in _render_directory
    changes = get_changes(self.env, repos, [i['rev'] for i in info])
  File "/home/trac/lib/python2.4/site-packages/trac/versioncontrol/web_ui/util.py", line 37, in get_changes
    changeset = repos.get_changeset(rev)
  File "/home/trac/lib/python2.4/site-packages/trac/versioncontrol/cache.py", line 41, in get_changeset
    self.sync()
  File "/home/trac/lib/python2.4/site-packages/TracPerforce-0.4.2-py2.4.egg/p4trac/api.py", line 227, in sync
  File "/home/trac/lib/python2.4/site-packages/TracPerforce-0.4.2-py2.4.egg/p4trac/api.py", line 203, in updateCache
  File "/home/trac/lib/python2.4/site-packages/TracPerforce-0.4.2-py2.4.egg/p4trac/api.py", line 153, in storeChangesInDB
  File "/home/trac/lib/python2.4/site-packages/trac/db/util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/home/trac/lib/python2.4/site-packages/trac/db/util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/home/trac/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 3111, in execute
    raise OperationalError, msg
OperationalError: ERROR:  duplicate key violates unique constraint "revision_pkey"

At this stage, the revision table in the postgres has this many rows ...

trac=> select count(*) from revision;
 count
-------
 35560
(1 row)

The last row has the max time as follows :

trac=> select max(time) from revision;
    max
------------
 1065487402
(1 row)

trac=> select count(*) from (select distinct(rev) from revision) as dist_rev_count;
 count
-------
 35560
(1 row)

Note that the node_change table has more than 2 million+ rows.

The above data indicates that trac is trying to resync from a given time and then gives up when it runs into a "duplicate" key violation.

Here are the issues :

1. Trac should not "give up" when it runs into a unique constraint violation while inserting rows into the revision table. It is also possible that this is a resync error.

An alternative is to *not* specify the revision(rev) as the primary index.

Also in postgres, there is a "copy to" command that takes in a text file and inserts the rows into the db. It is very fast. Tracd resync should implement that.

[anonymous]

It is also possible that multiple users may have selected browse source view leading to multiple insertions of the row with same rev id.

[anonymous]

Further revision number should be integer rather than text in table revision and node_change!

[mgood]

fixing formatting of description

[cboos]

An alternative is to *not* specify the revision(rev) as the primary index.

We're going to do that anyway for the sake of MySQL support (see #3676).

Further revision number should be integer rather than text in table revision and node_change!

rev ids are not always numbers (e.g. Monotone, Darcs, ...)

Anyway, I think you should report this error to TracHacks:PerforcePlugin, as the backend is responsible for creating the changesets that are later put into the cache. I can't really see why there could be duplicates for (rev,path,modif_type), so I think it's a bug in the backend. We recently had one of this kind in the Subversion backend (#3778).