Search code examples
javapostgresqloracle-cloud-infrastructure

Error when Inserting Data in PostgreSQL 15.3 from OCI Instance using Java: Returning Autogenerated Keys Issue

I have transferred my PostgreSQL database from an old server to an OCI (Oracle Cloud Infrastructure) instance. The old server was running PostgreSQL version 8.4, and the OCI instance has been upgraded to PostgreSQL 15.3. I'm currently using Java 8 and want to connect to the database using Java.

When I try to connect to the PostgreSQL database from my local machine, the connection is successful, and I can perform all operations. However, when I try to connect to the PostgreSQL database from the OCI instance, the connection works, and I can fetch and update data, but I encounter an error when trying to insert data. The error message is as follows:

### Error updating database. Cause: org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
### SQL: insert into t_buyer_cart (visitorno, chartno, lineno, thingno, flag, added_by, added_on, updated_by, updated_on, del_flag) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.; uncategorized SQLException for SQL []; SQL state [0A000]; error code [0]; Returning autogenerated keys is only supported for 8.2 and later servers.; nested exception is org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.

To resolve this issue, I have tried the following steps:

  1. Upgrading the JDBC version to the latest version (42.6.0).
  2. Downgrading the JDBC version.
  3. Checking the local machine connection.
  4. Checking detailed logs (confirming correct PostgreSQL and JDBC versions).
  5. Commenting out the return method (only trying to inserting data).
  6. Checking the API load balancer logs.
  7. Checking the PostgreSQL database logs.
  8. Allowing ingress and egress on port 5432 in the VCN (Virtual Cloud Network) settings.
  9. Checking the PostgreSQL configuration file for maximum connections and remote connection settings.
  10. Verifying that the telnet connection is working.
  11. Checking with the root user (postgres user).
  12. Verifying table rights for all users.
  13. Checking permissions for the JAR file and pom.xml file (both set to 777).

Despite trying these steps, the issue remains unresolved. Anyone have an idea behind the occurrence of this type of issue and how to resolve it?

Solution

  • I'm happy to report that we have successfully resolved the issue related to the JDBC driver in our project. I want to share the solution in case it helps others facing a similar problem.

    The Problem: We were using Maven to manage dependencies in our project, and we updated the JDBC driver version in the pom.xml file. Maven automatically downloaded the new JDBC driver JAR file and placed it in the /opt/tomcat/apache-tomcat-8/webapps/example/WEB-INF/lib directory. However, even though the correct version was downloaded, our application was not using the updated JDBC driver, and we encountered an error.

    The Solution: After some investigation, we discovered that the application was not picking up the JDBC driver from the /opt/tomcat/apache-tomcat-8/webapps/example/WEB-INF/lib directory as expected. Instead, we manually copied the JDBC driver JAR file to the /opt/tomcat/apache-tomcat-8/lib directory, which is the common library directory for Tomcat.

    By placing the JDBC driver in the common library directory and restarting Tomcat, the driver became available to all applications deployed on Tomcat, including our project. This approach resolved the version-related issue, and our application is now using the correct and updated JDBC driver successfully.