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

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

XMLWordPrintable

    • 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'

      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
  }
}

              jpechane Jiri Pechanec
              amit.goldi Amit Goldstein (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: