Issue: after upgrading the PostgreSQL database to v.10, the Ignition Gateway log posts errors containing the
org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
Solution: update the JDBC driver for PostgreSQL.
After upgrading the PostgreSQL database to v.10 (from one of the previous versions), the Ignition Gateway log posts errors when queries creating new rows are run, for example:
TagHistoryDatasourceSink 18Oct2017 13:24:04 Error modifying tag information in the database.
org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers
This message can be misleading as clearly PostgreSQL v. 10 is a later version than v. Postgres 8.2. The actual issue is a result of using an older JDBC driver to connect to your database. Ignition comes with JDBC drivers for major databases pre-installed, but when new database version that requires an updated driver is released by the database developer, this pre-packaged driver may end up not working for the newly released database. Since in this case the error messages are typically a result of some functionality that the older driver does not support and is unable to correctly deal with, the error messages returned to Ignition may potentially be unhelpful or misleading. The solution to the problem in cases like that is to update the JDBC driver to the latest version provided by the database developer.
To resolve the issue follow the steps below:
- Download the latest driver
You can obtain the latest driver (at the time of this writing, v. 42.1.4) here. Download the driver archive, extract it, and locate the .jar file (for example. postgresql-42.1.4.jar).
Note the path to the .jar file, you will need it in Step 4.
- Edit JDBC Driver
Open the Gateway Web interface and navigate to the Configure / Databases / Drivers page.
Click "Edit" next to the "PostgreSQL JDBC Driver" option to open the "Edit JDBC Driver" page.
- Upload the new .jar file
Click "Choose File" button of the "JAR File(s)" property, navigate to the .jar file you located in Step 2, and upload the file to the Gateway. This can take a few seconds.
- Save the changes to the "Edit JDBC Driver" page.
- Reinitialize the database connection
Navigate to the Configure / Databases / Connections, click the "Edit" button next your connection name, and save the connection (no changes are necessary).
You should now see that the new errors related to this database connections stopped appearing and that the data is now getting inserted into the database.