Sqlite to Postgresql migration invalidates id sequences

[kasper@…]

I'm experiencing a problem that I can see that should have been fixed before (#1715), but apparently it has not :(

Whenever I try to reply to a forum-topic I get an error like this:

IntegrityError: duplicate key value violates unique constraint "message_pkey"

From trac's log, here's the stack trace:

Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/web/main.py", line 432, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/web/main.py", line 204, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/core.py", line 77, in process_request
    return api.process_discussion(context) + (None,)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 62, in process_discussion
    is_moderator)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 697, in _do_action
    message['id'] or '-1', new_time, new_author, new_body)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 1134, in add_message
    context.cursor.execute(sql, (forum, topic, replyto, time, author, body))
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: duplicate key value violates unique constraint "message_pkey"

I recently migrated to use Postgresql and I think this is related(?).

[kasper@…]

Oh yes, and I'm using the latest version (I think?) of the DiscussionPlugin, I've installed using:

easy_install http://trac-hacks.org/svn/discussionplugin/0.11/

[Radek Bartoň]

Then it would be useful to attach debug log from the event, schema of your database and content of 'topic' and 'message' table. Information about exact Trac and PostgreSQL version may help too. Thanks.

[kasper@…]

I'm attaching the debug-log. I think this is perhaps what you're looking for?

2008-12-24 11:54:41,075 Trac[api] DEBUG: INSERT INTO message (forum, topic, replyto, time, author, body) VALUES (1, 43, 163, 1230116081, kasper, some message)

Trac version is: Trac-0.11.2.1-py2.5 Postgresql version is: PostgreSQL 8.3.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.2-1ubuntu11) 4.3.2

I'm also attaching topic.sql and message.sql, containing schema and content for the tables.

[kasper@…]

debug.log

[kasper@…]

schema and content for Topic and Message tables

[kasper@…]

I've identified the bug myself now actually!

There's a sequence called "message_id_seq" in the Postgresql schema, and this is the sequence that is used to increment the "id"-column. When I migrated the database to use postgresql instead of SQLite then this sequence still had a starting-point of 1 instead of "SELECT MAX(id)+1 FROM message":

CREATE SEQUENCE "PUBLIC".message_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1   <--- !!!!
  CACHE 1;
ALTER TABLE "PUBLIC".message_id_seq OWNER TO tracuser;

After changing the START attribute it works fine, but I'm not sure if this is something that could be fixed for future migrations?

I used the SqliteToPgScript script for the migration and what's weird is, that the other sequences (such as "ticket_id_seq") have a correct starting-point.

[kasper@…]

Here's some Postgresql SQL to fix the sequences:

forum_group_id_seq:

SELECT setval('"PUBLIC".forum_group_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".forum_group), true);

forum_id_seq:

SELECT setval('"PUBLIC".forum_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".forum), true);

message_id_seq:

SELECT setval('"PUBLIC".message_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".message), true);

[Radek Bartoň]

Great thanks, it's becase SqliteToPgScript migrate only Trac internal tables. There is nothing I can to about it in plugin, so I'm closing it.

[kasper@…]

Uh, forgot the topic_id_seq sequence:

SELECT setval('"PUBLIC".topic_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".topic), true);

I agree, I will tell the script guys about the problem and maybe they will come up with something.