[OSM-dev] Osmosis and Postgresql

[Tom Hughes]

On 08/07/09 09:10, Richard Fairhurst wrote:

> I get another constraint throwing an error whenever I try to write to the
> db:
>
> ERROR:  duplicate key value violates unique constraint "current_nodes_pkey1"
> : INSERT INTO "current_nodes" ("visible", "timestamp", "latitude",
> "changeset_id", "tile", "version", "longitude") VALUES('t', '2009-07-08
> 08:03:12.084512', 530909508, 5283, 2062370947, 1, -25840092) RETURNING "id"
>
> Slightly oddly, I also find that when I create a new user, it has id 1,
> whereas I'd have expected it to be maximum user id plus one.

This constraint violation is a side effect of the sequence issue I 
mentioned before. If you load data into the db then you need to reset 
the sequences. This SQL should reset all the sequence in the current schema:


select setval('acls_id_seq', (select max(id) from acls));
select setval('changesets_id_seq', (select max(id) from changesets));
select setval('countries_id_seq', (select max(id) from countries));
select setval('current_nodes_id_seq', (select max(id) from current_nodes));
select setval('current_relations_id_seq', (select max(id) from 
current_relations));
select setval('current_ways_id_seq', (select max(id) from current_ways));
select setval('diary_comments_id_seq', (select max(id) from 
diary_comments));
select setval('diary_entries_id_seq', (select max(id) from diary_entries));
select setval('friends_id_seq', (select max(id) from friends));
select setval('gpx_file_tags_id_seq', (select max(id) from gpx_file_tags));
select setval('gpx_files_id_seq', (select max(id) from gpx_file_ids));
select setval('messages_id_seq', (select max(id) from messages));
select setval('sessions_id_seq', (select max(id) from sessions));
select setval('user_tokens_id_seq', (select max(id) from user_tokens));
select setval('users_id_seq', (select max(id) from users));

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/