Duplicate key constraint violation when used with MasterTicketsPlugin

[smrobinson62@…]

I want to use BatchModify 0.2.0 to change the "blocking" custom field from MasterTicketsPlugin for a group of "child" tickets to have the value of the "parent" ticket. I have tried this with 6 "child" tickets blocking one "parent" several times. Each time at varying points in the update, Trac has the following problem (in trac.log). Sometimes only one of the six tickets is updated, sometimes two or three. I think this is due to the same field in a ticket being updated more than once in the same second as noted in this ticket ​http://trac.edgewall.org/ticket/6466 but I'm not sure. Should I report this in MasterTicketsPlugin too?

2009-07-13 18:38:27,305 Trac[main] ERROR: duplicate key violates unique constraint "ticket_change_pk"
Traceback (most recent call last):
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\web\main.py", line 423, in _dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\web\main.py", line 173, in dispatch chosen_handler)
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\web\main.py", line 286, in _pre_process_request
    chosen_handler = filter_.pre_process_request(req, chosen_handler)
  File "build\bdist.win32\egg\batchmod\web_ui.py", line 36, in pre_process_request
    self._batch_modify(req)
  File "build\bdist.win32\egg\batchmod\web_ui.py", line 69, in _batch_modify
    t.save_changes(req.authname, comment)
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\ticket\model.py", line 282, in save_changes
    listener.ticket_changed(self, comment, author, old_values)
  File "build\bdist.win32\egg\mastertickets\api.py", line 105, in ticket_changed
    links.save(author, comment, tkt.time_changed, db)
  File "build\bdist.win32\egg\mastertickets\model.py", line 69, in save
    (n, when_ts, author, field, old_value, new_value))
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\db\util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\db\util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: duplicate key violates unique constraint "ticket_change_pk"

[smrobinson62@…]

Here's a workaround my manager came up with. The idea is for a trigger to fire before INSERTing a row into ticket_change that deletes any existing rows with the same PK. The previous rows aren't really needed because the new row contains the most recent change for a field within the same second timestamp. The following code is for PostgreSQL but similar code should work with other DBs. We've tested it pretty carefully but not extensively. It does address the problem I reported in this ticket but it is clearly not a fix, just a workaround.

CREATE OR REPLACE FUNCTION ticket_change_validation()
  RETURNS "trigger" AS
$BODY$
BEGIN
	DELETE 	FROM ticket_change
	WHERE	ticket = NEW.ticket
	AND	"time"= NEW."time"
	AND 	field = NEW.field;
	RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION ticket_change_validation() OWNER TO postgres;

CREATE TRIGGER ticket_change_tr before INSERT on ticket_change FOR EACH ROW
EXECUTE PROCEDURE ticket_change_validation();

[anonymous]

This is actually a problem [1] with Trac itself. The granularity of the ticket change timestamp is one second, so when multiple changes get pushed to one ticket within one second the constraint in violated. That appears to be the case here. Comment 6 [2] on the ticket mentions that this problem affects batch modify when used with master tickets. It looks like this problem should be fixed in 0.12.

[1] ​http://trac.edgewall.org/ticket/6466 [2] ​http://trac.edgewall.org/ticket/6466#comment:6

[anonymous]

Closing this as won't fix since this seems to be a problem with Trac itself. If you can provide evidence otherwise feel free to reopen.