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

PostgreSQL ENUM default values are missing from generated schema

XMLWordPrintable

    • False
    • None
    • False
    • Hide

      Clone https://github.com/debezium/debezium-examples/tree/668de13eb5371092ac5b3d186a4cb61da791eab8/cloudevents

      Run

       

      export DEBEZIUM_VERSION=1.9
mvn clean install -f avro-data-extractor/pom.xml
docker-compose up --build

      Update the register-postgres-avro-avro.json file so it contains:

      {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "postgres",
    "database.dbname" : "postgres",
    "database.server.name": "dbserver3",
    "slot.name":"dbserver3",
    "schema.include.list": "inventory",
    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "key.converter.schemas.enable": "true",
    "key.converter.schema.registry.url": "http://schema-registry:8081",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schemas.enable": "true",
    "value.converter.schema.registry.url": "http://schema-registry:8081"
}

       

      Open the schema registry UI on localhost:8000 and change the dbserver3.inventory.customers-value schema's compatibility to FULL then run

      PGOPTIONS=--search_path=inventory psql -h localhost -U postgres

       

      postgres=# CREATE TYPE activity AS ENUM ('active', 'inactive');
CREATE TYPE
postgres=# alter table inventory.customers add column activity activity not null default 'active'::activity;
ALTER TABLE
postgres=# update inventory.customers set activity = 'inactive';
UPDATE 4

      Your connector should now be failing because the generated schema doesn't contain the default value, hence it doesn't respect a FULL compatibility.

       

       

      Show
      Clone https://github.com/debezium/debezium-examples/tree/668de13eb5371092ac5b3d186a4cb61da791eab8/cloudevents Run   export DEBEZIUM_VERSION=1.9 mvn clean install -f avro-data-extractor/pom.xml docker-compose up --build Update the register-postgres-avro-avro.json file so it contains: {     "connector.class" : "io.debezium.connector.postgresql.PostgresConnector" ,     "tasks.max" : "1" ,     "database.hostname" : "postgres" ,     "database.port" : "5432" ,     "database.user" : "postgres" ,     "database.password" : "postgres" ,     "database.dbname" : "postgres" ,     "database.server.name" : "dbserver3" ,     "slot.name" : "dbserver3" ,     "schema.include.list" : "inventory" ,     "key.converter" : "io.confluent.connect.avro.AvroConverter" ,     "key.converter.schemas.enable" : " true " ,     "key.converter.schema.registry.url" : "http: //schema-registry:8081" ,     "value.converter" : "io.confluent.connect.avro.AvroConverter" ,     "value.converter.schemas.enable" : " true " ,     "value.converter.schema.registry.url" : "http: //schema-registry:8081" }   Open the schema registry UI on localhost:8000 and change the dbserver3.inventory.customers-value schema's compatibility to FULL then run PGOPTIONS=--search_path=inventory psql -h localhost -U postgres   postgres=# CREATE TYPE activity AS ENUM ( 'active' , 'inactive' ); CREATE TYPE postgres=# alter table inventory.customers add column activity activity not null default 'active' ::activity; ALTER TABLE postgres=# update inventory.customers set activity = 'inactive' ; UPDATE 4 Your connector should now be failing because the generated schema doesn't contain the default value, hence it doesn't respect a FULL compatibility.    

      What Debezium connector do you use and what version?

      debezium-connector-postgres-1.9.0.Final

      What is the connector configuration?

       

      {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "postgres",
    "database.dbname" : "postgres",
    "database.server.name": "dbserver3",
    "slot.name":"dbserver3",
    "schema.include.list": "inventory",
    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "key.converter.schemas.enable": "true",
    "key.converter.schema.registry.url": "http://schema-registry:8081",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schemas.enable": "true",
    "value.converter.schema.registry.url": "http://schema-registry:8081"
}

       

      What is the captured database version and mode of deployment?

      First encountered the issue in RDS but I can reproduce using your example repository:

      https://github.com/debezium/debezium-examples/tree/668de13eb5371092ac5b3d186a4cb61da791eab8/cloudevents

      So Postgres 14.2

      What behaviour do you expect?

      When adding a text column with a default value, like this:

      alter table inventory.customers add column some_txt text not null default 'hello';

      and inserting a new row, using the schema registry UI you can see the schema being updated:

      But if you do the same thing with an enum then you do not get a default value:

       

       

      CREATE TYPE activity AS ENUM ('active', 'inactive');
alter table inventory.customers add column activity activity not null default 'active'::activity;

       

      This is particularly problematic when trying to add an enum to a schema that's configured with a FULL compatibility level.

       

       

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

      Haven't tried it but I do not see anything in the changelog that would indicate this issue is fixed.

       

       

        1. image-2022-04-25-14-21-42-320.png
          12 kB
          Michel ANTOINE
        2. image-2022-04-25-14-22-56-435.png
          18 kB
          Michel ANTOINE

              ccranfor@redhat.com Chris Cranford
              antoin-m Michel ANTOINE (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated:
                Resolved: