PostgreSQL Error with LTree

[Robin Tang]

Hey folks,

I'm observing this error on Debezium Server 2.0 and 2.2.

In the same `postgres` example image, I modify to add a `ltree` column, and Debezium is returning null.

```

CREATE EXTENSION IF NOT EXISTS ltree;

ALTER TABLE inventory.customers ADD COLUMN ltree_test ltree;

UPDATE inventory.customers set ltree_test = 'Top' where id = 1001;

UPDATE inventory.customers set ltree_test = 'Top.Science' where id = 1002;

UPDATE inventory.customers set ltree_test = 'Top.Science.Astrononmy4' where id = 1003;

```

Error:

debezium-server               | {"timestamp":"2023-05-20T18:52:07.367Z","sequence":206,"loggerClassName":"org.slf4j.impl.Slf4jLogger","loggerName":"io.debezium.connector.postgresql.TypeRegistry","level":"INFO","message":"Failed to obtain SQL type information for type ltree via custom statement, falling back to TypeInfo#getSQLType()","threadName":"debezium-postgresconnector-dbserver1-change-event-source-coordinator","threadId":31,"mdc":{"dbz.taskId":"0","dbz.databaseName":"postgres","dbz.connectorName":"dbserver1","dbz.connectorType":"Postgres","dbz.connectorContext":"streaming"},"ndc":"","hostName":"95f36458be77","processName":"io.debezium.server.Main","processId":1}

Any ideas why this is happening?

Thanks,

Robin

[jiri.p...@gmail.com]

Hi,

this should be irrelevant to the fact it is Debezium Server. I tried it with ou tutorial and it seems to be working. Could you please try it yourself and check if it works on tutorial for you and if not then provide the exact steps for us to reproduce, esp. when should be each action done.

Thanks

[Robin Tang]

Hey Jiri,

Here's a repro video - https://www.loom.com/share/bf6a05ab5ba843aeb1fe7e7d8ab7024b

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/218ec467-e899-4de7-bed1-98073b64c93dn%40googlegroups.com.

[Robin Tang]

I am currently debugging this and I suspect the culprit is because `data` is actually NULL.

I am printing the contents of this particular line https://github.com/debezium/debezium/blob/155207dc9bd3d0dfe62d0485dd00e6a0219742e9/debezium-connector-postgres/src/main/java/io/debezium/connector/postgresql/PostgresValueConverter.java#L629

logger.info("#### ltree col:" + column + "### data: " + data + "### fieldDefn " + fieldDefn);

debezium-server               | {"timestamp":"2023-05-31T17:26:10.272Z","sequence":211,"loggerClassName":"org.slf4j.impl.Slf4jLogger","loggerName":"io.debezium.connector.postgresql.PostgresValueConverter","level":"INFO","message":"#### ltree col:ancestry_path ltree(2147483647, 0) DEFAULT VALUE NULL### data: null### fieldDefn Field{name=ancestry_path, index=4, schema=Schema{io.debezium.data.Ltree:STRING}}","threadName":"debezium-postgresconnector-dbserver1-change-event-source-coordinator","threadId":31,"mdc":{"dbz.taskId":"0","dbz.databaseName":"postgres","dbz.connectorName":"dbserver1","dbz.connectorType":"Postgres","dbz.connectorContext":"streaming"},"ndc":"","hostName":"239e59ee80e5","processName":"io.debezium.server.Main","processId":1}

Any idea why this is happening?

Thanks in advance!

Robin

[Robin Tang]

Just found out more.

1) This is an issue with streaming ongoing LTree column changes.

2) Initial snapshot is actually fine

3) When I added debezium.source.include.unknown.datatypes=true which will include all unknown data types and just cast it as a STRING, it works.

Seems to me like there's a bug with custom data types <> streaming.

[Chris Cranford]

Hi Robin -

I just checked our "ltree" test and I concur I see the same behavior, it gets exported successfully during snapshot without the unknown data-type toggle, but is omitted during streaming.  Ideally this should work similarly regardless of phase, could you please raise a Jira?

Thanks,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/3ae59662-a782-42ce-a30d-adc088254e1dn%40googlegroups.com.

[Robin Tang]

Yep https://issues.redhat.com/browse/DBZ-6524.