Opened 8 years ago

Closed 7 years ago

# Duplicate key constraint violation when used with MasterTicketsPlugin

Reported by: Owned by: smrobinson62@… anonymous normal BatchModifyPlugin normal 0.11

### Description

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
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"


### comment:1 Changed 8 years ago by 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();


### comment:2 Changed 7 years ago by anonymous

Owner: changed from ashwin_phatak to anonymous new → assigned

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.

Last edited 2 years ago by Ryan J Ollos (previous) (diff)

### comment:3 Changed 7 years ago by anonymous

Resolution: → wontfix assigned → closed

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.

### Modify Ticket

Change Properties