[BUG?] Violation of PRIMARY KEY constraint each time the sourceTagPath change for a reference tag

Ignition 8.1.28

Hello,

We have some reference tags used with the following pattern :

  • reference tags "mytagA" : sourcetagPath : "mytagA_pri" or "mytagA_sec"
  • opc tags "mytagA_pri"
  • opc tags "mytagA_sec"

We have some history configured for "mytagA"

Each time we change the sourcetagPath of "mytagA" some exception "Violation of PRIMARY KEY constraint"
This change trigger the history insert and because "mytagA_pri" and "mytagA_sec" have the same Value/Quality/Timestamp an exception is generated.

It seems to be a bug. The change of "sourcetagPath" for reference tags must not trigger an history recorded if the target Value/Quality/Timestamp has no change !

java.lang.RuntimeException: java.sql.BatchUpdateException: Batch entry 3 INSERT INTO sqlt_data_1_2023_06("tagid", "intvalue", "floatvalue", "stringvalue", "datevalue", "dataintegrity", "t_stamp") VALUES(5387,0,NULL,NULL,NULL,192,1686031757943) was aborted: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « sqlt_data_1_2023_06_pkey » Détail : La clé « (tagid, t_stamp)=(5387, 1686031757943) » existe déjà. Call getNextException to see other errors in the batch.

at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:47)

at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source)

at java.base/java.util.stream.ReferencePipeline$2$1.accept(Unknown Source)

at java.base/java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)

at java.base/java.util.stream.ReferencePipeline$2$1.accept(Unknown Source)

at java.base/java.util.stream.Streams$StreamBuilderImpl.forEachRemaining(Unknown Source)

at java.base/java.util.stream.ReferencePipeline$Head.forEach(Unknown Source)

at java.base/java.util.stream.ReferencePipeline$7$1.accept(Unknown Source)

at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.copyInto(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)

at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source)

at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.evaluate(Unknown Source)

at java.base/java.util.stream.ReferencePipeline.forEach(Unknown Source)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:186)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:156)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:180)

at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:147)

Caused by: java.sql.BatchUpdateException: Batch entry 3 INSERT INTO sqlt_data_1_2023_06("tagid", "intvalue", "floatvalue", "stringvalue", "datevalue", "dataintegrity", "t_stamp") VALUES(5387,0,NULL,NULL,NULL,192,1686031757943) was aborted: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « sqlt_data_1_2023_06_pkey » Détail : La clé « (tagid, t_stamp)=(5387, 1686031757943) » existe déjà. Call getNextException to see other errors in the batch.

at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)

at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2367)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2099)

at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1456)

at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1481)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:546)

at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:893)

at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916)

at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684)

at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)

at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)

at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:60)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:767)

at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink$BatchedTagInsert.onComplete(TagHistoryDatasourceSink.java:1296)

at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.insertTagValues(TagHistoryDatasourceSink.java:1088)

at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:550)

at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:525)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.lambda$storeToDatasource$2(AbstractDatasourceSink.java:186)

at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:45)

... 18 common frames omitted

Caused by: org.postgresql.util.PSQLException: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « sqlt_data_1_2023_06_pkey » Détail : La clé « (tagid, t_stamp)=(5387, 1686031757943) » existe déjà.

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)

After some discussion with IA support, it seems perhaps to be better to use derived tags with history instead of reference tags with history.
We will try.