IntegrityError: duplicate key violates unique constraint
|Reported by:||shanec@…||Owned by:||shanec@…|
trac.db: Added a method update_sequence() to database connections to allow updating table sequences during schema updates. 
This may be a postgres specific issue, we have not tested with sqlite or mysql.
There are kind of two issues here depending on the table structure. One is for tables that use serialized (auto-increment) id columns, the other is for tables like the session table which has a primary key which is not auto incremented. I believe the second is not an issue rlated to this bug, but other regular bugs such as what was fixed in #3563. So far, I've only identified this in tables with primary keys on auto increment fields.
This is caused (or most easily reproduced) by how upgrades to database tables happens in trac+plugins. The problem occurs when a plugin changes a table schema. The typical formula is to move the current table to a temp table, create the new table, and then copy the data from the temp to the new table. If the table has primary keys that are sequences, and those keys are inserted into the new table, the problem will occur. Here is any easy (directly in sql) reproduction:
First, create the table as a plugin would initially do it:
set search_path to public; CREATE TABLE test_keys ( id serial NOT NULL, foo text, CONSTRAINT id_pk PRIMARY KEY (id) );
Next, add data:
set search_path to public; INSERT INTO test_keys (foo) VALUES('test');
Now, later the plugin needs an upgrade, so we recreate that:
set search_path to public; CREATE TEMPORARY TABLE test_keys_old AS SELECT * FROM test_keys; DROP TABLE test_keys; CREATE TABLE test_keys ( id serial NOT NULL, foo text, bar text, CONSTRAINT id_pk PRIMARY KEY (id) ); INSERT INTO test_keys (id, foo) SELECT id, foo FROM test_keys_old; DROP TABLE test_keys_old;
Alls good, right? Wrong.
set search_path to public; INSERT INTO test_keys (foo, bar) VALUES('test2', 'this stinks');
You now get the error:
ERROR: duplicate key value violates unique constraint "id_pk"
And here's the fix:
SELECT setval('test_keys_id_seq', (SELECT MAX(id) FROM test_keys)+1)
What happened? During the upgrade we copied the rows from the temp table to the new table, including the ID column. Since we inserted the id, the new sequence (test_keys_id_seq) is not updated correctly. Any time you manually add a value to an auto sequenced column, you must manually update the sequence value. We're unaware of any postgres setting that would "fix" this on the postgres side.
Change History (14)
Changed 4 years ago by bobbysmith007@…
comment:11 Changed 3 years ago by rblank
- API Changes modified (diff)
- Resolution set to fixed
- Status changed from new to closed