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

Null values in "before" are populated with "__debezium_unavailable_value"

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 0.10.0.Final
    • Fix Version/s: 1.0.0.Beta3
    • Component/s: postgresql-connector
    • Labels:
      None
    • Environment:

      Postgres 9.6
      Wal2json - commit id 9e962bad61ef2bfa53747470bac4d465e71df880

    • Steps to Reproduce:
      Hide

      1. Create a toastable table with replica identity full, and a column that is nullable (context_id in this example):

      CREATE TABLE customer
      (
          id              UUID PRIMARY KEY,
          name            VARCHAR(64) NOT NULL,
          context_id    VARCHAR(64),
          note              TEXT
      );
      
      ALTER TABLE customer
          REPLICA IDENTITY FULL;
      

      2. Insert values, do not insert value to the nullable column

      insert into public.customer ( id, name) values ( '63e7e88d-e42f-409c-9934-6add4e7c6e38', 'Customer name')
      

      3. Update values, do not update the nullable column

      update public.customer set name='new name' where public.customer.id = '63e7e88d-e42f-409c-9934-6add4e7c6e38'
      
      Show
      1. Create a toastable table with replica identity full, and a column that is nullable (context_id in this example): CREATE TABLE customer ( id UUID PRIMARY KEY , name VARCHAR (64) NOT NULL , context_id VARCHAR (64), note TEXT ); ALTER TABLE customer REPLICA IDENTITY FULL ; 2. Insert values, do not insert value to the nullable column insert into public .customer ( id, name ) values ( '63e7e88d-e42f-409c-9934-6add4e7c6e38' , 'Customer name ' ) 3. Update values, do not update the nullable column update public .customer set name = ' new name ' where public .customer.id = '63e7e88d-e42f-409c-9934-6add4e7c6e38'

      Description

      When a toastable column is created as null and not changed during update, the value under the "before" section is expected to be null. Instead it is
      "__debezium_unavailable_value"

      The table is toastable and with REPLICA IDENTITY FULL.

      Seems like the culprit is in wal2json that does not send the column in the old payload. PostgresChangeRecordEmitter#columnValues() does not find the values in cache so it opts to use "__debezium_unavailable_value"

      Example results:

      {
        "schema": {
          "type": "struct",
          "fields": [
            {
              "type": "struct",
              "fields": [
                {
                  "type": "string",
                  "optional": false,
                  "name": "io.debezium.data.Uuid",
                  "version": 1,
                  "field": "id"
                },
                {
                  "type": "string",
                  "optional": true,
                  "field": "name"
                },
                {
                  "type": "string",
                  "optional": true,
                  "field": "context_id"
                }
              ],
              "optional": true,
              "name": "customers.public.customer.Value",
              "field": "before"
            },
            {
              "type": "struct",
              "fields": [
                {
                  "type": "string",
                  "optional": false,
                  "name": "io.debezium.data.Uuid",
                  "version": 1,
                  "field": "id"
                },
                {
                  "type": "string",
                  "optional": true,
                  "field": "name"
                },
                {
                  "type": "string",
                  "optional": true,
                  "field": "context_id"
                }
              ],
              "optional": true,
              "name": "customers.public.customer.Value",
              "field": "after"
            },
            {
              "type": "struct",
              "fields": [
                {
                  "type": "string",
                  "optional": false,
                  "field": "version"
                },
                {
                  "type": "string",
                  "optional": false,
                  "field": "connector"
                },
                {
                  "type": "string",
                  "optional": false,
                  "field": "name"
                },
                {
                  "type": "int64",
                  "optional": false,
                  "field": "ts_ms"
                },
                {
                  "type": "string",
                  "optional": true,
                  "name": "io.debezium.data.Enum",
                  "version": 1,
                  "default": "false",
                  "field": "snapshot"
                },
                {
                  "type": "string",
                  "optional": false,
                  "field": "db"
                },
                {
                  "type": "string",
                  "optional": false,
                  "field": "schema"
                },
                {
                  "type": "string",
                  "optional": false,
                  "field": "table"
                },
                {
                  "type": "int64",
                  "optional": true,
                  "field": "txId"
                },
                {
                  "type": "int64",
                  "optional": true,
                  "field": "lsn"
                },
                {
                  "type": "int64",
                  "optional": true,
                  "field": "xmin"
                }
              ],
              "optional": false,
              "name": "io.debezium.connector.postgresql.Source",
              "field": "source"
            },
            {
              "type": "string",
              "optional": false,
              "field": "op"
            },
            {
              "type": "int64",
              "optional": true,
              "field": "ts_ms"
            }
          ],
          "optional": false,
          "name": "customers.public.customer.Envelope"
        },
        "payload": {
          "before": {
            "id": "63e7e88d-e42f-409c-9934-6add4e7c6e38",
            "name": "Customer name",
            "context_id": "__debezium_unavailable_value"
          },
          "after": {
            "id": "63e7e88d-e42f-409c-9934-6add4e7c6e38",
            "name": "new name",
            "context_id": null
          },
          "source": {
            "version": "0.10.0.Final",
            "connector": "postgresql",
            "name": "customers",
            "ts_ms": 1571743715582,
            "snapshot": "false",
            "db": "customers",
            "schema": "public",
            "table": "customer",
            "txId": 594,
            "lsn": 24239672,
            "xmin": null
          },
          "op": "u",
          "ts_ms": 1571743715593
        }
      }
      
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                amit.goldi Amit Goldstein
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: