Data Insertion error (duplicate key value violates unique constraint "seriesidentity")

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Data Insertion error (duplicate key value violates unique constraint "seriesidentity")

Derek Hohls
Hi

I had to upgrade a SOS install to version 4.3.16 (I previously had a much older version of 4.3.x running) and now the InsertObservations, that were previously working, are failing.

The error trace is below.  I am not sure why (featureofinterestid, observablepropertyid, procedureid) is triggering this; almost every new observation is going to have this same combination of keys - so why is the SOS trying to insert them every time?

I see there was one mention of this on the code repo (https://github.com/52North/SOS/pull/407) but that was from 2016, so I am not sure it is related?

Any help appreciated - I need to get this working.

Thanks
Derek

Error trace:

Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.series (featureOfInterestId, observablePropertyId, procedureId, offeringId, deleted, published, firstTimeStamp, lastTimeStamp, firstNumericValue, lastNumericValue, unitId, seriesId) values (4, 99, 26, 22, 'F', 'T', NULL, NULL, NULL, NULL, NULL, 2881) was aborted: ERROR: duplicate key value violates unique constraint "seriesidentity"
  Detail: Key (featureofinterestid, observablepropertyid, procedureid)=(4, 99, 26) already exists.  Call getNextException to see other errors in the batch.
    at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:145) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2156) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:460) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:793) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1659) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:127) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:114) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:484) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.n52.sos.ds.hibernate.dao.series.AbstractSeriesDAO.getOrInsert(AbstractSeriesDAO.java:180) ~[hibernate-common-4.3.16.jar:na]
    at org.n52.sos.ds.hibernate.dao.series.SeriesDAO.getOrInsertSeries(SeriesDAO.java:92) ~[hibernate-common-4.3.16.jar:na]
    at org.n52.sos.ds.hibernate.dao.series.AbstractSeriesObservationDAO.addObservationIdentifiersToObservation(AbstractSeriesObservationDAO.java:96) ~[hibernate-common-4.3.16.jar:na]
    at org.n52.sos.ds.hibernate.dao.AbstractObservationDAO.insertObservationSingleValue(AbstractObservationDAO.java:577) ~[hibernate-common-4.3.16.jar:na]
    at org.n52.sos.ds.hibernate.InsertObservationDAO.insertObservation(InsertObservationDAO.java:209) ~[hibernate-dao-4.3.16.jar:na]
    ... 44 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "seriesidentity"
  Detail: Key (featureofinterestid, observablepropertyid, procedureid)=(4, 99, 26) already exists.




_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Data Insertion error (duplicate key value violates unique constraint "seriesidentity")

Carsten Hollmann
Hi Derek,

from SOS version 4.3.7 to 4.3.8 we have added the offering to the series
table. So the unique constraint "seriesidentity" also contains the offering.

A update script for PostgreSQL is provided in the sources and you find
it here on GitHub:

https://github.com/52North/SOS/blob/maintenance/4.3/misc/db/PostgreSQL/series/PG_437_to_438.sql

Best,
Carsten
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines