Debezium and handling Postgres 'infinity' timestamps

[Brandon Harvey]

Hello,

We're looking for some assistance with handling Postgres 'infinity' and '-infinity' timestamps. We've gone through the Debezium docs on timestamps and have found some points of interest. Firstly it looks like the docs only mention handling of 'infinity' based timestamps in the "master" version (https://debezium.io/documentation/reference/connectors/postgresql.html#postgresql-timestamp-type). No previous versions (e.g. https://debezium.io/documentation/reference/1.5/connectors/postgresql.html#postgresql-timestamp-type) make mention of it which leads us to believe that this functionality is only supported in the absolute latest version of Debezium (i.e. v1.5.0.Beta1), is this true?

Additionally we've inspected what we believe to be the code for handling the 'infinity' timestamps here https://github.com/debezium/debezium/blob/v1.5.0.Beta1/debezium-connector-postgres/src/main/java/io/debezium/connector/postgresql/PostgresValueConverter.java#L963-L968

Our stack's relevant component versions:

Kafka cluster=2.6.0

dbz-kafka-connect=1.4.1.Final

apicurio=1.3.2.Final

postgres=v12.4

Our debezium configuration, note we're using pgoutput which from my understanding should allow Debezium to handle parsing of native Postgres datatypes i.e. 'infinity'.

connector.class=io.debezium.connector.postgresql.PostgresConnector

max.queue.size=8192

key.converter.apicurio.registry.url=<redacted>

value.converter.apicurio.registry.url=<redacted>

slot.name=debezium_ledger

tasks.max=1

hstore.handling.mode=json

publication.name=dbz_​publication

transforms=route

snapshot.delay.ms=300

transforms.route.type=io.debezium.transforms.ByLogicalTableRouter

transforms.route.topic.regex=([^.]+)\.([^.]+)\.([^.]+)

transforms.route.topic.replacement=$3

value.converter=io.apicurio.registry.utils.converter.AvroConverter

database.whitelist=<redacted>

snapshot.fetch.size=10240

key.converter=io.apicurio.registry.utils.converter.AvroConverter

database.user=debezium

database.dbname=<redacted>

value.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy

database.server.name=<redacted>

database.port=<redacted>

plugin.name=pgoutput

schema.whitelist=<redacted>

table.exclude.list=<redacted>

database.hostname=<redacted>

database.password=<redacted>

key.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy

max.batch.size=2048

snapshot.mode=exported

We're receiving the following error with the Debezium connector whenever attempting to ingest timestamps involving 'infinity' or '-infinity' values. I can post the full stacktrace upon request but keeping it top-level to shorten this post.

2021-03-05 18:51:25,740 ERROR  Postgres|infra_staging|postgres-connector-task  Cannot parse time/date value '-infinity', expected format 'y..y-MM-dd HH:mm:ss[.S]'   [io.debezium.connector.postgresql.connection.wal2json.DateTimeFormat$ISODateTimeFormat]

2021-03-05 18:51:25,740 ERROR  Postgres|infra_staging|postgres-connector-task  Producer failure   [io.debezium.pipeline.ErrorHandler]

org.apache.kafka.connect.errors.ConnectException: Error while processing event at offset {transaction_id=null, lsn_proc=4600524136, lsn_commit=4600523704, lsn=4600524136, txId=41015, ts_usec=1614970285607405}

In summary we're curious as to why despite us specifying Debezium should use the 'pgoutput' plugin it still throws errors when ingesting native Postgres datatypes (i.e. 'infinity' timestamps). The stacktrace also appears to include mentions of wal2json, with our understanding we'd expect to only see 'pgoutput' here.

[Brandon Harvey]

It was recently found that our initial snapshot completes successfully. This includes tables with a timestamp value of 'infinity' or '-infinity'. It looks like DBz is able to convert those values fine during the snapshot process but not during streaming.

both 'inifinity' and '-infinity' were converted to bigint with value of 9223372036854775807. Once streaming, attempting to make any changes to DB rows involving 'infinity' or '-infinity' fail with the error posted previously

[Gunnar Morling]

Hi,

Could you please test with Debezium 1.5.0.Beta1? Support for the infinity types was just recently added via https://issues.redhat.com/browse/DBZ-2614.

Best,

--Gunnar

[Angel Torres]

Hi guys, Im having the same issue with the latest release of debezium.. 

The error im facing is this one:
 Cannot parse time/date value '-infinity', expected format 'y..y-MM-dd[ GG]' (io.debezium.connector.postgresql.connection.wal2json.DateTimeFormat$ISODateTimeFormat:153)

This is happening during connector initialization.

The connector options are:
connector.class=io.debezium.connector.postgresql.PostgresConnector
slot.name=folio_replication_slot
tasks.max=1
internal.key.converter.schemas.enable=false
decimal.handling.mode=double
heartbeat.action.query=INSERT INTO debezium_heartbeat (heartbeat) VALUES ('thump')
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter=org.apache.kafka.connect.json.JsonConverter
publication.autocreate.mode=filtered
database.dbname=folio
database.user=debezium
database.history.kafka.bootstrap.servers=xxxx
time.precision.mode=connect
database.server.name=folio
heartbeat.interval.ms=30000
database.port=5432
plugin.name=pgoutput
internal.key.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=false
database.hostname=xxxx
database.password=xxxx
internal.value.converter.schemas.enable=false
internal.value.converter=org.apache.kafka.connect.json.JsonConverter
value.converter.schemas.enable=false
table.include.list=public.folio, public.folioitem, public.folioitemdetail, public.foliotaxitemattributes, public.advanceddeposit, public.paidfolioitem, public.paymentdistribution
snapshot.mode=never

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

Could you please share the full log? The line number in the exception definitely does not match witt the latest release code.

Thanks

J.