Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-5473

The array_position function breaks PostgreSQL 9.6 compatibility

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Do
    • Icon: Critical Critical
    • None
    • 1.9.5.Final
    • postgresql-connector
    • None
    • False
    • None
    • False
    • Hide
      1. docker run -d --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 debezium/zookeeper:latest
      2. docker run -d --name kafka -p 9092:9092 --link zookeeper:zookeeper debezium/kafka:latest
      3. docker run -d --name connect -p 8083:8083 --link kafka:kafka -e BOOTSTRAP_SERVERS=kafka:9092 -e GROUP_ID=sonae_group -e CONFIG_STORAGE_TOPIC=sonae_storage_topic -e OFFSET_STORAGE_TOPIC=sonae_offset_topic debezium/connect:latest
      4. curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" localhost:8083/connectors/ -d '{"name": "store1", "config": {"connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.hostname": "10.126.86.10", "database.port": "5432", "database.user": "postgres", "database.password": "alterego123", "database.dbname" : "novounifodb", "database.server.name": "store1"}}'
      Show
      docker run -d --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 debezium/zookeeper:latest docker run -d --name kafka -p 9092:9092 --link zookeeper:zookeeper debezium/kafka:latest docker run -d --name connect -p 8083:8083 --link kafka:kafka -e BOOTSTRAP_SERVERS=kafka:9092 -e GROUP_ID=sonae_group -e CONFIG_STORAGE_TOPIC=sonae_storage_topic -e OFFSET_STORAGE_TOPIC=sonae_offset_topic debezium/connect:latest curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" localhost:8083/connectors/ -d '{"name": "store1", "config": {"connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.hostname": "10.126.86.10", "database.port": "5432", "database.user": "postgres", "database.password": "alterego123", "database.dbname" : "novounifodb", "database.server.name": "store1"}}'

      In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.

      Bug report

      For bug reports, provide this information, please:

      What Debezium connector do you use and what version?

      PostgreSQL connector and docker debezium/connect:latest (1.9.5.Final)

      What is the connector configuration?

      docker run -d --name connect -p 8083:8083 --link kafka:kafka -e BOOTSTRAP_SERVERS=kafka:9092 -e GROUP_ID=sonae_group -e CONFIG_STORAGE_TOPIC=sonae_storage_topic -e OFFSET_STORAGE_TOPIC=sonae_offset_topic debezium/connect:latest

       

      {
          "name": "store1",
          "config":

      {         "connector.class": "io.debezium.connector.postgresql.PostgresConnector",         "database.hostname": "10.126.86.10",         "database.port": "5432",         "database.user": "postgres",         "database.password": "alterego123",         "database.dbname": "novounifodb",         "database.server.name": "store1"     }

      }

      What is the captured database version and mode of depoyment?

      On-premises PostgreSQL database  version 9.4.21.

      What behaviour do you expect?

      Debezium 1.9 should still work with PG 9.6 (or 9.4.21 in my case) which doesn't have the function call array_position.

      What behaviour do you see?

      The issue here is that a function call is being used in the connection validation, array_position, seen here and this function is only compatible on PG 10+.

      https://zulip-uploads.s3.amazonaws.com/38476/BFFdLZ1_p4K04Z6xrOBupLVu/image.png?AWSAccessKeyId=AKIAIEVMBCAT2WD3M5KQ&Signature=3qdFbPZDQF%2FGkr8Y6PxsHSCwT3A%3D&Expires=1659609124 

      This was changed in DBZ-4710. It probably should not have added this as a part of Debezium 1.9. It makes sense for Debezium 2.0 since I believe PG10+ is being targetted in that new major version.

      Do you see the same behaviour using the latest relesead Debezium version?

      Yes.

      Do you have the connector logs, ideally from start till finish?

      Yes.

      connect.log

      Feature request or enhancement

      For feature requests or enhancements, provide this information, please:

      Which use case/requirement will be addressed by the proposed feature?

      Usage of the Debezium PostgreSQL connector on databases with in a version (9.6 for example) that doesn't have the array_position function available.

        1. image-2022-08-05-17-05-37-890.png
          image-2022-08-05-17-05-37-890.png
          18 kB
        2. connect-1.log
          206 kB
        3. connect.log
          195 kB

              Unassigned Unassigned
              ejcosta-sonaemc Ernesto Costa (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: