BUG #18495: invalid type mapping for timestamptz from call of: getMetaData and then geColumns on PgConnection.

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jurajburian(at)gmail(dot)com
Subject: BUG #18495: invalid type mapping for timestamptz from call of: getMetaData and then geColumns on PgConnection.
Date: 2024-06-05 13:21:12
Message-ID: 18495-46c579d44724e872@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18495
Logged by: Juraj Burian
Email address: jurajburian(at)gmail(dot)com
PostgreSQL version: 16.0
Operating system: linux
Description:

Hello folks,

I can see a non correct type mapping for timestamptz type. Code snippets
used in descriptions are pure SQL or (Scala). We use latest JDBC driver
(version 42.7.3).
The bug is simple. Instead of Types.TIMESTAMP_WITH_TIMEZONE we obtain value
of Types.TIMESTAMP in resultset from getMetadata wit consequential call of:
geColumns.

Here description in more detail form:

1) Lets define table like this :
create table if not exists l1.serving_end (
id int8 not null,
actor_date timestamptz(0) not null
)
2) call meta = connection.getMetadata, then rs = meta.geColumns(null, "l1",
"serving_end", null), and then examine result set (rs).
in our case we use code like this to fix the problem :
val dataType = {
val dt = rs.getInt("DATA_TYPE")
if (dt == Types.TIMESTAMP && typeName == "timestamptz") {
log.warn(
s"Invalid datatype mapping for timestamptz: $dt instead of
${Types.TIMESTAMP_WITH_TIMEZONE} for: $table:$columnName"
)
Types.TIMESTAMP_WITH_TIMEZONE
} else dt

so we replace: Types.TIMESTAMP to correct value
Types.TIMESTAMP_WITH_TIMEZONE in case of timestamtz.
We can see warning like this:
WARN kafka-to-ca-dwh - Invalid datatype mapping for timestamptz: 93 instead
of 2014 for: l1.serving_end:actor_date.
The code indicates that the mapping for timestamptz is invalid. For other
type mappings we do not observe any problems.

Thanks a lot for looking on this problem.
Best regards
Juraj

p.s we use statement.setObject(..., type) method, our fix also fixed
behaviour in insert/update for values of timestamptz -
java.time.OffsetDateTime is parsed correctly.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2024-06-05 15:39:44 Re: BUG #18377: Assert false in "partdesc->nparts >= pinfo->nparts", fileName="execPartition.c", lineNumber=1943
Previous Message Michael Paquier 2024-06-05 11:19:42 Re: Minor update on pg_hba documentation