Store & Forward Not Passing To Postgres

Ignition
1

PostgreSQL 10.4 | Ignition Platform 7.9.8 (running on trial)

Store and Forward seems to only be sending data to quarantine and not to the DB. The tables appear to be in the DB but there are not any values.

There are two errors in the log that are occurring :

DatasourceForwardTransaction
org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
at org.postgresql.jdbc3.AbstractJdbc3Statement.addReturning(AbstractJdbc3Statement.java:153)
at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:363)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:458)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:394)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.prepareStatement(SRConnectionWrapper.java:428)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.prepareStatmentForGeneratedKeys(SRConnectionWrapper.java:433)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepInsertGetKey(SRConnectionWrapper.java:250)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.getIdOfScanclassName(TagHistoryDatasourceSink.java:587)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.updateSCExecRecord(TagHistoryDatasourceSink.java:545)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:476)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:458)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:137)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:115)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:170)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:155)

…and…

MemoryForwardTransaction
org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
at org.postgresql.jdbc3.AbstractJdbc3Statement.addReturning(AbstractJdbc3Statement.java:153)
at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:363)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:458)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:394)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.prepareStatement(SRConnectionWrapper.java:428)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.prepareStatmentForGeneratedKeys(SRConnectionWrapper.java:433)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepInsertGetKey(SRConnectionWrapper.java:250)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.getIdOfScanclassName(TagHistoryDatasourceSink.java:587)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.updateSCExecRecord(TagHistoryDatasourceSink.java:545)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:476)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:458)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:137)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:115)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:170)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:155)

Thank you for looking through this. This is my first project with Ignition so I may have missed something obvious.

2

Check out this KBA:
https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/122/0/database-connection-errors-after-upgrading-postgresql-to-v10

1 Like
3

Worked perfectly, thank you! I thought I had checked all the resources but somehow missed the Knowledgebase:)

1 Like
4

I applied the knowledge Article referenced, but I am still getting the “Returning autogenerated keys is only supported for 8.2 and later servers” error. When I retry the quarantined items, they attempt to process, but then cycle back. I should add that this is a new system. It has not previously worked.

image
image1920Ă—939 132 KB

5

If you follow the KB and it isn’t working for you, check the user-lib/jdbc directory for the old postgres JDBC file still existing. We have seen an issue where the old JDBC driver is not removed when it replaced and it is still being used instead of the new one.

The fix is to remove the old driver from this folder if it still exists.

Garth

6

Was having the same issue as OP. Following your advice rectified the issue. Thank You

7

Please update the KB article to reflect the possibility that you will need to delete the old .jar file.

I just spent hours looking for a solution and all it took was to delete that old file.

1 Like