Kafka-connect-jdbc: JDBC sink connector doesn't support tables using declarative partitioning in Postgresql 10.

[bli24]

Postgresql 10 added support for Declarative Partitioning, https://www.postgresql.org/docs/10/static/ddl-partitioning.html. It appears that the sink connector doesn't support this new table type. It can't retrieve the meta data of the table, and throws the exception that the table doesn't exist.

[bli24]

Here's the Exception that was thrown.

org.apache.kafka.connect.errors.ConnectException: Table cdrin is missing and auto-creation is disabled
at io.confluent.connect.jdbc.sink.DbStructure.create(DbStructure.java:85)
at io.confluent.connect.jdbc.sink.DbStructure.createOrAmendIfNecessary(DbStructure.java:62)
at io.confluent.connect.jdbc.sink.BufferedRecords.add(BufferedRecords.java:66)
at io.confluent.connect.jdbc.sink.JdbcDbWriter.write(JdbcDbWriter.java:62)
at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:66)
at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:435)
at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:251)
at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:180)
at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:148)
at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:146)
at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:190)
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)

[Anthares]

I hate digging out old stuff, but I encoutered the same issue on Postgres 11.

Since data written in the table can become huge over time, it seems a valid use case to partition this table.
Is there any plan to fix this issue ?

Regards,

EDIT :
After more research in the kafka-connect-jdbc source code, it fully relies on the jdbc driver to check if table exists.
Current version (5.3.0) relies on old version of postgres jdbc driver (9.4-1206-jdbc41).

So for those facing the same issue, upgrade postgres driver to 42.1.2 or higher for PG 10+

I've made some test both locally and on production kafka-connect, and it runs fine.

[timtebeek]

I see the postgresql driver version has since been upgraded to 42.2.10:
https://github.com/confluentinc/kafka-connect-jdbc/blob/master/pom.xml#L62

When running confluentinc/cp-kafka-connect:5.5.1 I indeed see:
/usr/share/java/kafka-connect-jdbc/postgresql-42.2.10.jar.

Appears as though this issue has been resolved and can be closed.

[echang0929]

Actually, I tested confluentinc/cp-kafka-connect:5.4.1 could support PostgreSQL partition tables, although cp-kafka-connect:5.4.0 could not.
I found Kafka Connect 5.4.1 has added PostgreSQL jdbc driver version 42.2.10
root@app-ccpes-cp-kafka-connect-596d6bc9c-5796h:/usr/share/java/kafka-connect-jdbc# ls -l total 8408 -rw-r--r-- 1 root root 17555 Mar 2 23:51 common-utils-5.4.1.jar -rw-r--r-- 1 root root 317816 Mar 2 23:51 jtds-1.3.1.jar -rw-r--r-- 1 root root 228872 Mar 2 23:51 kafka-connect-jdbc-5.4.1.jar -rw-r--r-- 1 root root 927447 Mar 2 23:51 postgresql-42.2.10.jar -rw-r--r-- 1 root root 41139 Mar 2 23:51 slf4j-api-1.7.26.jar -rw-r--r-- 1 root root 7064881 Mar 2 23:51 sqlite-jdbc-3.25.2.jar
It supports PostgreSQL partition tables very well.

[hisahin]

I needed this case for normal table to partitioned table migration. I got same error.
If you use later version than 42.2.12, it still does not work.
But works well with postgresql-42.2.10.jar.

https://github.com/pgjdbc/pgjdbc/pull/1708/files

[echang0929]

@hisahin , Please refer to my post: https://github.com/confluentinc/kafka-connect-jdbc/issues/1092