Opened 8 years ago

Closed 7 years ago

Last modified 21 months ago

#5505 closed defect (wontfix)

Duplicate key constraint violation when used with MasterTicketsPlugin

Reported by: smrobinson62@… Owned by: anonymous
Priority: normal Component: BatchModifyPlugin
Severity: normal Keywords:
Cc: Trac Release: 0.11


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 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\", line 423, in _dispatch_request
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\web\", line 173, in dispatch chosen_handler)
  File "C:\Python25\lib\site-packages\trac-0.11.1-py2.5.egg\trac\web\", line 286, in _pre_process_request
    chosen_handler = filter_.pre_process_request(req, chosen_handler)
  File "build\bdist.win32\egg\batchmod\", line 36, in pre_process_request
  File "build\bdist.win32\egg\batchmod\", 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\", line 282, in save_changes
    listener.ticket_changed(self, comment, author, old_values)
  File "build\bdist.win32\egg\mastertickets\", line 105, in ticket_changed, comment, tkt.time_changed, db)
  File "build\bdist.win32\egg\mastertickets\", 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\", 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\", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: duplicate key violates unique constraint "ticket_change_pk"

Attachments (0)

Change History (3)

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
	DELETE 	FROM ticket_change
	WHERE	ticket = NEW.ticket
	AND	"time"= NEW."time"
	AND 	field = NEW.field;
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
Status: newassigned

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 21 months ago by Ryan J Ollos (previous) (diff)

comment:3 Changed 7 years ago by anonymous

Resolution: wontfix
Status: assignedclosed

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
Set your email in Preferences
as closed The owner will remain anonymous.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment

E-mail address and name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.