Loading...

Type: Bug
Resolution: Done
Priority: Major
Fix Version/s: 1.0.0.Beta3
Affects Version/s: 0.10.0.Final
Component/s: postgresql-connector
Labels:
None
Environment:

Postgres 9.6
Wal2json - commit id 9e962bad61ef2bfa53747470bac4d465e71df880

Git Pull Request:
https://github.com/debezium/debezium/pull/1123
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'

SFDC Cases Counter:
SFDC Cases Open:
SFDC Cases Links:

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

Details

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates