debezium / debezium/dbz

Postgres Connector: Array of TIME(p): fractions of seconds are lost for p > 3 and DateTimeException when JVM user.timezone is not UTC

Open
#2,559 1 comment 0 reactions 0 assignees View on GitHub
component/postgresql-connector type/bug
Dominant language
HTML
Stars
6
Forks
8
Avg merge
2d 19h
Merged PRs (30d)
1

Description

## Bug report

**What Debezium connector do you use and what version?**

PostgreSQL connector version 3.5.2.Final

---

**What is the connector configuration?**

The same as tutorial + `time.precision.mode` option
```
{
"name": "inventory-connector",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"tasks.max": "1",
"database.hostname": "postgres",
"database.port": "5432",
"database.user": "postgres",
"database.password": "postgres",
"database.dbname" : "postgres",
"topic.prefix": "dbserver1",
"schema.include.list": "inventory",
"time.precision.mode": "isostring"
}
}
```

---

**What is the captured database version and mode of deployment?**

To compose this bug report, I reproduce the bug using [tutorial deployment of Debezium](https://github.com/debezium/debezium-examples/blob/main/tutorial/README.md#using-postgres).

I also set JVM `user.timezone=GMT+3`

---

**What behavior do you expect?**

For example I have a table:

```
create table public.date_time_dbz_conversions_test (time_6_array time(6)[]);

INSERT INTO public.date_time_dbz_conversions_test VALUES ('{"02:59:59.999999", "02:59:59.999999"}');
INSERT INTO public.date_time_dbz_conversions_test VALUES ('{"03:00:00.000000", "03:00:00.000000"}');
INSERT INTO public.date_time_dbz_conversions_test VALUES ('{"03:00:00.123456", "03:00:00.123456"}');
```

I expect Debezium to produce 3 create messages with `after` values:
1. `{"time_6_array":["02:59:59.999999Z","02:59:59.999999Z"]}`
2. `{"time_6_array":["03:00:00Z","03:00:00Z"]}`
3. `{"time_6_array":["03:00:00.123456Z","03:00:00.123456Z"]}`

---

**What behavior do you see?**

1. `{"time_6_array":null}` (array value is lost, because of DateTimeException)
2. `{"time_6_array":["03:00:00Z","03:00:00Z"]}` (OK, as expected)
3. `{"time_6_array":["03:00:00.123Z","03:00:00.123Z"]}` (microseconds are lost)

DateTimeException does not happen if I set `-Duser.timezone=UTC`. It happens when time is less than timezone offset, when java.sql.Time.getTime() returns negative value.

I see the same behaviour for `time.precision.mode` = `adaptive` (both lost microseconds and DateTimeException) and `connect` (DateTimeException).

You can find stack trace in logs below.

---

**Do you see the same behaviour using the latest released Debezium version?**

I checked versions 3.6.1.Final and 3.7.0.Beta1 using the tutorial deployment.

---

**Do you have the connector logs, ideally from start till finish?**

[logs.txt](https://github.com/user-attachments/files/31773941/logs.txt)

You can find DateTimeException on line 11992

---

**How to reproduce the issue using our [tutorial](https://github.com/debezium/debezium-examples/tree/main/tutorial) deployment?**

1. To reproduce lost microseconds: just run Debezium Tutorial -> Using Postgres and capture `time(6)[]` values with microseconds (example below).
2. To reproduce DateTimeException also add `KAFKA_OPTS=-Duser.timezone=GMT+3` in environment of `connect` service in `docker-compose-postgres.yml`
```
drop table inventory.customers cascade;
create table inventory.customers (time_6_array time(6)[]);

INSERT INTO inventory.customers VALUES ('{"02:59:59.999999", "02:59:59.999999"}');
INSERT INTO inventory.customers VALUES ('{"03:00:00.000000", "03:00:00.000000"}');
INSERT INTO inventory.customers VALUES ('{"03:00:00.123456", "03:00:00.123456"}');
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.