Trouble Charting New Pens

Unable to add new pens using the easy chart on a linux system using Postgres DB. Added new DB but still the same issue. Pens that were trending still trends ok. New pens does show up in the Easy Chart Customizer but not in the edit pen data tag path. Store Forward ERROR: relation “sqlth_te_“id"seq" does not exist
9:29:08 PM History.SQLTags.DataLoader.mydb_Loader No tag information could be found for any of the specified paths. Check that paths are correct. Paths: [oscili]
9:29:08 PM StoreAndForward.mydb.MemoryStore.MemoryForwardTransaction Error forwarding data: ERROR: relation "sqlth_scinfo
"id”_seq” does not exist

org.postgresql.util.PSQLException: ERROR: relation “sqlth_scinfo_“id”_seq” does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:803)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runScalarPrepQuery(SRConnectionWrapper.java:192)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runScalarQuery(SRConnectionWrapper.java:181)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.fetchGeneratedKey(SRConnectionWrapper.java:236)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepInsertGetKey(SRConnectionWrapper.java:210)
at com.inductiveautomation.ignition.gateway.history.sinks.TagHistoryDatasourceSink.getIdOfScanclassName(TagHistoryDatasourceSink.java:352)
at com.inductiveautomation.ignition.gateway.history.sinks.TagHistoryDatasourceSink.updateExecAndGetSCID(TagHistoryDatasourceSink.java:322)
at com.inductiveautomation.ignition.gateway.history.sinks.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:228)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:120)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:97)
at com.inductiveautomation.ignition.gateway.history.sinks.AggregateSink.storeData(AggregateSink.java:142)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:134)

It appears that the main problem is with the query that is supposed to return the new key value for inserted rows. Postgres has only recently added support for “returning autogenerated keys”, but before that we had to query the latest sequence value for the new id. The problem is that the query tries to use the column quote character around the column name, and that doesn’t work here.

So, you could try the following:

  1. Go to Database>Drivers in the gateway.
  2. Click on “Translators”
  3. Edit the postgres translator, and remove the column quote character (just make it blank).
  4. Save

The problem, though, is that this might interfere with queries that require the quote char to be in place. If that’s the case, your other option is to upgrade the Postgres JDBC driver:

  1. Download the file that I’ve put up on our server here (postgres-9.0-801.jdbc4.jar)
  2. Place that in the directory: {install dir}\contexts\main\jdbc. Delete the old Postgres file.
  3. Restart your gateway to load the new file instead.
  4. Edit the translator as before, but this time select “Supports returning auto-generated keys”.
    This should also work. In this case, you can still have the column quote character set.

Hope this helps,

Fantastic! Thank you very much. The first option worked. The only issue now is that there are two store and forward engines. One is the new database the other is an old data base that has been deleted. How can the old data base store and forward engine be removed?

The store and forward engine should get removed when the database connection is deleted. Is it still there after you restart the gateway? If so, we might need to manually remove it from the internal database.

Regards,

yes the deleted data base store and forward engine is still listed.

Continued from this thread.

I have done the first step (removed the ") and that worked.

Are you going to package the new jdbc file in 7.3?