Kafka-connect-jdbc: org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" does not exist

[mihbor]

Encountered this exception on one of our jdbc sink connectors.

[2018-10-04 09:49:05,132] INFO Checking PostgreSql dialect for existence of table ... (io.confluent.connect.jdbc.dialect.PostgreSqlDatabaseDialect)
[2018-10-04 09:49:05,134] INFO Using PostgreSql dialect table ... present (io.confluent.connect.jdbc.dialect.PostgreSqlDatabaseDialect)
[2018-10-04 09:49:05,139] INFO Setting metadata for table ... (io.confluent.connect.jdbc.util.TableDefinitions)
[2018-10-04 09:49:05,160] WARN Write of 30 records failed, remainingRetries=10 (io.confluent.connect.jdbc.sink.JdbcSinkTask)
java.sql.BatchUpdateException: Batch entry 0 INSERT ... was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2778)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:395)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1912)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
    at io.confluent.connect.jdbc.sink.BufferedRecords.flush(BufferedRecords.java:138)
    at io.confluent.connect.jdbc.sink.JdbcDbWriter.write(JdbcDbWriter.java:66)
    at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:75)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:564)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:322)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:225)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:193)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
[2018-10-04 09:49:05,161] INFO Initializing writer using SQL dialect: PostgreSqlDatabaseDialect (io.confluent.connect.jdbc.sink.JdbcSinkTask)
[2018-10-04 09:49:05,161] ERROR WorkerSinkTask{id=...-0} RetriableException from SinkTask: (org.apache.kafka.connect.runtime.WorkerSinkTask)
org.apache.kafka.connect.errors.RetriableException: java.sql.SQLException: java.sql.BatchUpdateException: Batch entry 0 INSERT ... was aborted.  Call getNextException to see the cause.
org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" does not exist

    at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:94)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:564)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:322)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:225)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:193)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

Deleting and re-creating the connector doesn't help and neither does restarting the worker.
This is issue seems to be related to the postgres jdbc driver issue:
https://github.com/pgjdbc/pgjdbc/issues/443
reported against driver version 9.4-1206 which kafka-connect-jdbc is using.
Indeed, after replacing the postgres driver jar with version 9.4-1207 the issue disappears.

[cameronbraid]

I had a similar issue, upgrading the postgresql driver fixed it for me too

[rhauch]

Thanks for the report, @mihbor, and @cameronbraid for confirming that upgrading the driver addresses the issue.

I just merged the referenced PR to upgrade the driver to 9.4.1212, and merged to the 5.0.x, 5.1.x, 5.2.x, and 5.3.x and master branches. This change will appear in the next patch release on any of these lines.