wall2json message not completely pushed for update operation (long text not pushed)

[suganda7]

Hi team, I found an issue when updating the row for update operation (long text).
here is the test chronology :

• PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
• wal2json11.x86_64 2.3-2.rhel7 @pgdg11

• CREATE TABLE test ( user_id serial PRIMARY KEY, username VARCHAR ( 50 ), description text );

• select pg_create_logical_replication_slot('testing', 'wal2json');

• INSERT INTO public.test (username, description) VALUES('foo',$Put Long text more than 8000 character);

• SELECT data FROM pg_logical_slot_get_changes('testing', null, null);
  Data pushed perfectly (I can see long string in the message) ✅

• Update only one column
  UPDATE public.test SET username='hello' WHERE user_id=1;

• SELECT data FROM pg_logical_slot_get_changes('testing', null, null);

 {
  "change": [
    {
      "kind": "update",
      "schema": "public",
      "table": "test",
      "columnnames": [
        "user_id",
        "username"
      ],
      "columntypes": [
        "integer",
        "character varying(50)"
      ],
      "columnvalues": [
        1,
        "hello"
      ],
      "oldkeys": {
        "keynames": [
          "user_id"
        ],
        "keytypes": [
          "integer"
        ],
        "keyvalues": [
          1
        ]
      }
    }
  ]
}

description column is missing in the message. and for the short text all working fine.

is that expected behavior ? and can we fix this ?

thanks

[eulerto]

Yes. That's the way logical decoding works. It does not include unchanged TOAST columns (your long description seems to be stored as a TOAST value) for performance reasons. Because of that, wal2json does not include that column in the JSON output. However, if you modify the description value, wal2json will include it (see TOAST tests).

In summary, it is a logical decoding limitation, it is not a wal2json bug. When Postgres supports it, wal2json will certainly include these columns too.

[suganda7]

thank you @eulerto

[mertant]

That's the way logical decoding works. It does not include unchanged TOAST columns

What constitutes an "unchanged toast"?

If I do UPDATE SET my_toastable_column = my_toastable_column - in other words, perform a "no-op" update on the toast - will it then be considered a "changed toast" even though there is no diff between the old and new value?

If that is the case, then a possible workaround could be: when performing an update operation modifying a non-toast column, set the toast column to the value it already is.