使用JOOQ+H2(Postgres模式)+TIMESTAMP WITH TIMEZONE时遇转换错误

我近期将数据库列类型从TIMESTAMP改为TIMESTAMP WITH TIME ZONE，模型类对应字段从LocalDateTime改为OffsetDateTime以匹配JOOQ生成代码的类型。当前使用最新版JOOQ及Postgres模式的H2数据库，尝试向USER_MODEL表保存数据时，出现「CHARACTER LARGE OBJECT转换为TIMESTAMP WITH TIME ZONE」的数据转换错误。

错误栈信息 #

Caused by: io.r2dbc.h2.H2DatabaseExceptionFactory$H2R2dbcDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:
select "id" from final table (merge into "user_model" using (select cast($1 as varchar(1000000000)) "email", cast($2 as varchar(1000000000)) "password", cast($3 as varchar(1000000000)) "role", cast($4 as varchar(1000000000)) "tier", cast($5 as varchar(1000000000)) "first_name", cast($6 as varchar(1000000000)) "last_name", cast($7 as boolean) "enabled", cast($8 as timestamp(9) with time zone) "created_at", cast($9 as timestamp(9) with time zone) "updated_at", cast($10 as smallint) "total_retries") "t" on "user_model"."id" = cast($11 as uuid) when matched then update set "user_model"."email" = $12, "user_model"."password" = $13, "user_model"."role" = $14, "user_model"."tier" = $15, "user_model"."first_name" = $16, "user_model"."last_name" = $17, "user_model"."enabled" = $18, "user_model"."created_at" = $19, "user_model"."updated_at" = $20, "user_model"."total_retries" = $21 when not matched then insert ("email", "password", "role", "tier", "first_name", "last_name", "enabled", "created_at", "updated_at", "total_retries") values ("t"."email", "t"."password", "t"."role", "t"."tier", "t"."first_name", "t"."last_name", "t"."enabled", "t"."created_at", "t"."updated_at", "t"."total_retries")) "user_model" [22018-230]
    at io.r2dbc.h2.H2DatabaseExceptionFactory.convert(H2DatabaseExceptionFactory.java:60) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at io.r2dbc.h2.client.SessionClient.query(SessionClient.java:138) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at io.r2dbc.h2.H2Statement.lambda$execute$2(H2Statement.java:145) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:113) ~[reactor-core-3.6.8.jar:3.6.8]
    ... 71 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:

表创建SQL #

CREATE TABLE user_model (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY ,
  email TEXT NOT NULL CONSTRAINT user_model_email CHECK (LENGTH(email) <= 320 ),
  password TEXT NOT NULL CONSTRAINT user_model_password CHECK (LENGTH(password) <= 50),
  role TEXT NOT NULL CONSTRAINT user_model_role CHECK (LENGTH(role) <= 50),
  tier TEXT NOT NULL CONSTRAINT user_model_tier CHECK (LENGTH(tier) <= 50),
  first_name TEXT NOT NULL CONSTRAINT user_model_first_name CHECK (LENGTH(first_name) <= 50),
  last_name TEXT NOT NULL CONSTRAINT user_model_last_name CHECK (LENGTH(last_name) <= 50),
  enabled BOOLEAN NOT NULL,
  created_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
  total_retries SMALLINT NOT NULL CONSTRAINT user_model_total_retries CHECK (total_retries >= 0)
);

UserModel代码 #

data class UserModel(
    override var id: UUID? = null,
    val email: String?,
    var password: String,
    var role: RoleDto,
    var firstName: String?,
    var lastName: String?,
    var enabled: Boolean,
    var createdAt: OffsetDateTime?,
    var updatedAt: OffsetDateTime?,
    var totalRetries: Long = 0,
    var tier: UserTier
) : BaseModel(id)

执行的保存代码 #

val record = ctx.newRecord(table, model)
val id: UUID = ctx.insertInto(table)
    .set(record)
    .onConflict()
    .doUpdate()
    .set(record)
    .returning(idField)
    .awaitFirst()
    .getValue(idField)!!

模型及记录数据 #

UserModel(id=null, email=email@gmail.com, password=abcd=, role=USER, firstName=Michael, lastName=Name, enabled=false, createdAt=2024-08-10T19:49:33.731172500+01:00, updatedAt=2024-08-10T19:49:33.731172500+01:00, totalRetries=0, tier=FREE)
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|ID    |EMAIL                       |PASSWORD                                     |ROLE |TIER |FIRST_NAME|LAST_NAME|ENABLED|CREATED_AT                          |UPDATED_AT                          |TOTAL_RETRIES|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|{null}|*example@gmail.com|*yODttTr/abcd=|*USER|*FREE|*Michael  |*Name|*false |*2024-08-10T19:49:33.731172500+01:00|*2024-08-10T19:49:33.731172500+01:00|           *0|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+

• H2 Postgres模式的类型兼容缺陷：H2在模拟Postgres模式时，对TIMESTAMP WITH TIME ZONE类型的处理与原生Postgres存在差异。当传递OffsetDateTime参数时，H2错误地将其识别为字符大对象（CLOB）类型，而非正确的带时区时间戳，导致转换失败。

• JOOQ与R2DBC-H2的类型映射不匹配：最新版JOOQ将TIMESTAMP WITH TIME ZONE映射为OffsetDateTime，但R2DBC-H2驱动在Postgres模式下，没有正确完成OffsetDateTime到数据库类型的转换，反而将其序列化为字符串（CLOB），触发了H2的类型转换错误。

• MERGE语句的CAST逻辑触发错误：从生成的SQL可以看到，JOOQ对created_at和updated_at字段做了cast($8 as timestamp(9) with time zone)操作，但如果传入的参数本身是CLOB类型，H2无法直接将其转换为带时区的时间戳，这是错误的直接触发点。之前使用TIMESTAMP+LocalDateTime时，H2的类型映射更直接，不存在该兼容问题。

内容的提问来源于stack exchange，提问作者Michael