osmosis icon indicating copy to clipboard operation
osmosis copied to clipboard
verified publisher icon openstreetmap

ERROR: duplicate key value violates unique constraint "way_tags_pkey" Detail: Key (way_id, version, k)=(1571192, 1, SEMANTIC) already exists

Open ahuigo opened this issue 4 years ago • 1 comments

When I use osmosis to upload xml to postgre db. There is an error as the title described :

$ osmosis --read-xml file=tmp/tmp_upload_934962040 --write-apidb database=my_db host=mydb.com:5432 user=pguser password= validateSchemaVersion=no
Jun 11, 2021 6:55:06 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.48.3
Jun 11, 2021 6:55:06 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jun 11, 2021 6:55:06 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jun 11, 2021 6:55:06 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.


Jun 11, 2021 6:58:29 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to bulk insert way tags into the database.
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.flushWayTags(ApidbWriter.java:777)
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.addWayTags(ApidbWriter.java:1252)
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.flushWays(ApidbWriter.java:724)
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1238)
	at org.openstreetmap.osmosis.core.container.v0_6.WayContainer.process(WayContainer.java:58)
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1172)
	at org.openstreetmap.osmosis.xml.v0_6.impl.WayElementProcessor.end(WayElementProcessor.java:117)
	at org.openstreetmap.osmosis.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:107)
	at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
	at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
	at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
	at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
	at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
	at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
	at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
	at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
	at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
	at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
	at java.xml/javax.xml.parsers.SAXParser.parse(SAXParser.java:197)
	at org.openstreetmap.osmosis.xml.v0_6.impl.BaseXMLReader.parseXML(BaseXMLReader.java:65)
	at org.openstreetmap.osmosis.xml.v0_6.impl.BaseXMLReader.handleXML(BaseXMLReader.java:77)
	at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:52)
	at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "way_tags_pkey"
 ​Detail: Key (way_id, version, k)=(1571192, 1, SEMANTIC) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
	at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.flushWayTags(ApidbWriter.java:771)
	... 22 more

Jun 11, 2021 6:58:29 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
	at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
	at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
	at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
	at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330)
	at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
	at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
	at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
	at org.codehaus.classworlds.Launcher.main(Launcher.java:47)

What puzzled me is that way_id=1571192 do not exist in way_tags even after this error occurs.

Why?

my_db=> insert into way_tags(way_id,version,k,v) values(1571192, 1, 'SEMANTIC','');
INSERT 0 1
my_db=>
my_db=> insert into way_tags(way_id,version,k,v) values(1571192, 1, 'SEMANTIC','');
ERROR:  duplicate key value violates unique constraint "way_tags_pkey"
DETAIL:  Key (way_id, version, k)=(1571192, 1, SEMANTIC) already exists.
my_db=> \d way_tags;
                  Table "public.way_tags"
Column  |       Type        | Collation | Nullable | Default
---------+-------------------+-----------+----------+---------
way_id  | bigint            |           | not null | 0
k       | character varying |           | not null |
v       | character varying |           | not null |
version | bigint            |           | not null |
Indexes:
   "way_tags_pkey" PRIMARY KEY, btree (way_id, version, k)
Foreign-key constraints:
   "way_tags_id_fkey" FOREIGN KEY (way_id, version) REFERENCES ways(way_id, version)

ahuigo avatar Jun 11 '21 11:06 ahuigo

Resolved! The reason is my data has two same tags "SEMANTIC".

<way id="1571192" version="1" timestamp="2021-06-11T06:39:00Z" uid="1" user="yxh" changeset="1">
    <nd ref="1571188"/>
    <nd ref="1571189"/>
    <nd ref="1571190"/>
    <nd ref="1571191"/>
    <tag k="OBJECT_TYPE_NAME" v="RoadMark"/>
    <tag k="SEMANTIC" v=""/>
    <tag k="SUB_TYPE_NAME" v="GuideArrow"/>
    <tag k="CONFIDENCE" v="1.0"/>
    <tag k="name" v="RoadMarkId(557040077, 29076)"/>
    <tag k="SEMANTIC" v="GoStraight"/>
  </way>

ahuigo avatar Jun 11 '21 12:06 ahuigo