Postgres Connector: Array of TIME(p): fractions of seconds are lost for p > 3 and DateTimeException when JVM user.timezone is not UTC
- 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
Assessment
This issue has not been assessed yet.