we no longer ever return TIMESTAMP_WITH_TIMEZONE

[davecramer]

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java

Line 3384 in f2d8ec5

if (sqlType == Types.TIMESTAMP_WITH_TIMEZONE || sqlType == Types.TIMESTAMP) {

          Oid.TIMESTAMPTZ_ARRAY},

Does anyone remember why we changed this ?

[vlsi]

Does anyone remember why we changed this ?

Could you clarify?

if (sqlType == Types.TIMESTAMP_WITH_TIMEZONE || sqlType == Types.TIMESTAMP) { was introduced in #474 instead of Driver.notImplemented

[davecramer]

We never return sqlType = Types.TIMESTAMP_WITH_TIMEZONE

 {"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp",
      Oid.TIMESTAMPTZ_ARRAY},

[vlsi]

So the code would be fine even if we flip the switch =)

[davecramer]

I'm implementing ZonedTimeStamp which should only be for TIMESTAMP_WITH_TIMEZONE but we never return it. Curious what would break...

[paulmiddelkoop]

@davecramer I encounter a problem that I cannot use Spring's SimpleJdbcInsert since the column type of a "timestamptz" is determined incorrectly as Types.TIMESTAMP instead of Types. TIMESTAMP_WITH_TIMEZONE. My OffsetDateTime is then processed incorrectly in PgPreparedStatement.setObject.

Is it possible to return the correct SQL type?

Stacktrace:

Caused by: org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}
	at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:349)
	at org.postgresql.jdbc.TimestampUtils.toTimestamp(TimestampUtils.java:380)
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:631)
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:907)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:437)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:238)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:169)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert.setParameterValues(AbstractJdbcInsert.java:604)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert.access$100(AbstractJdbcInsert.java:62)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert$3.setValues(AbstractJdbcInsert.java:580)
	at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:960)
	at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
	... 47 more
Caused by: java.lang.NumberFormatException: Trailing junk on timestamp: 'T15:51:56.841+01:00'
	at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:339)
	... 60 more

[davecramer]

@paulmiddelkoop I'd have to see the code here. It is possible to read a timestamptz. This bug refers to the code, not that we never return a timestamptz

[paulmiddelkoop]

@davecramer I think it does. SimpleJdbcInsert (and also JOOQ, I noticed yesterday) use metadata to determine column types. Since the JDBC driver never return "timestamptz" due to below code, the exception above pops up:

{"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMPTZ_ARRAY},

[vlsi]

@paulmiddelkoop , would you please clarify which exact pgjdbc version are you using?

[davecramer]

@paulmiddelkoop https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/TimestampTest.java#L514 s
seems to be testing timestamps with time zone

[paulmiddelkoop]

@vlsi I'm using the latest version: 42.1.4

@davecramer that tests if the ResultSet.getTimeStamp() method works, the problem is not in there.

The problem is that framework code, like Spring's SimpleJdbcInsert, first asks the datatype of a column. With that datatype is calls PreparedStatement.setObject. So something like this happens:

preparedStatement.setObject("columName", myOffsetDateTime, 93) 

This will fail since Postgres cannot handle OffsetDateTime for type 93.

Therefor SQL Type 2013 should be returned for timestamp with time zone since that is the actual type and would make the setObject succeed.

The incorrect mapping is defined in TypeInfoCache:

{"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMPTZ_ARRAY},

[davecramer]

@paulmiddelkoop thanks for the clarification.

[paulmiddelkoop]

@davecramer Any idea if you guys have some time to look at this issue?

[davecramer]

@paulmiddelkoop So in fact it should be returning 2014 which is TIMESTAMP_WITH_TIMEZONE.

This is interesting as it is 1.8 and newer, so we would end up with different behaviour depending on the version of java...

@vlsi thoughts on implementing this given the different behaviour with different versions?

[vlsi]

@davecramer , frankly speaking I do not see why TIMESTAMP_WITH_TIMEZONE.

@paulmiddelkoop says the problem is pgjdbc does not handle setObject(int, OffsetDateTime, Types.TIMESTAMP) kind of API.
This is to be tested and implemented independently from TIMESTAMP_WITH_TIMEZONE, and I think OffsetDateTime, Types.TIMESTAMP) makes perfect sense.

@marschall , any corrections?

[paulmiddelkoop]

@vlsi I think technically the bug is that pgjdbc returns an incorrect data type. Frameworks like Spring and JOOQ will then call a setObject with an incorrect data type. You could indeed also support OffsetDateTime in the setObject for a Timestamp. That sounds like a work-around instead of a real solution since the problem still exists. Other code that depends on a correct data type determination will also not work correctly.

[davecramer]

@paulmiddelkoop can you test pr1060 ?

[vlsi]

That sounds like a work-around instead of a real solution since the problem still exists

As far as I understand, Spring Framework does not have TIMESTAMP_WITH_TIMEZONE in the sources:

https://github.com/spring-projects/spring-framework/search?utf8=%E2%9C%93&q=TIMESTAMP_WITH_TIMEZONE&type=

Hibernate ORM does not have that either: https://github.com/hibernate/hibernate-orm/search?utf8=%E2%9C%93&q=TIMESTAMP_WITH_TIMEZONE&type=

On the other hand,
Both of those libraries do mention Types.TIMESTAMP

https://github.com/spring-projects/spring-framework/search?utf8=%E2%9C%93&q=%22Types.TIMESTAMP%22&type=

https://github.com/hibernate/hibernate-orm/search?utf8=%E2%9C%93&q=%22Types.TIMESTAMP%22&type=

I bet that if pgjdbc would produce TIMESTAMP_WITH_TIMEZONE, then it would break either the libraries, or the application code.
Even though "_WITH_TIMEZONE" is technically correct, it is never tested in the real world

[vlsi]

paulmiddelkoop : The problem is that framework code, like Spring's SimpleJdbcInsert, first asks the datatype of a column

That is broken by design.
SQL is a type-strict language, and, theoretically you can have multiple functions that differ in argument types only.

For instance to_string(timestamp) and to_string(number)

Suppose the insert is insert into my_table(str) values (to_string(?))
Now is the question: what is the expected "data type" for the bind placeholder?
Theoretically, you should be able to call different functions depending on the Types.... argument of the setObject (or the relevant setInt vs setTimestamp method)

Yes, there are times when you can "request the expected data types" out of a SQL statement, however it is broken to ask data types on the fly just in order to use them in setObject.

Relevant part of ParameterMetaData documentation:

For some queries and driver
implementations, the data that would be returned by a ParameterMetaData
object may not be available until the PreparedStatement has
been executed.

Some driver implementations may not be able to provide information about the types and properties for each parameter marker in a CallableStatement object.

[vlsi]

My OffsetDateTime is then processed incorrectly in PgPreparedStatement.setObject.

@paulmiddelkoop , would you please clarify where do you get OffsetDateTime from?
pgjdbc returns OffsetDateTime only in case it is asked that with getObject(int, OffsetDateTime.class)

[paulmiddelkoop]

@vlsi I have a POJO containing an OffsetDateTime which I try to insert with SimpleJdbcInsert, so I got the OffsetDateTime myself.

SimpleJdbcInsert is called simple because it's simple and does not support functions so I think using types is not broken.

I agree that changing the returned data type will indeed break down Hibernate except when they are willing to change the Postgres Dialect. See this thread without an conclusion: https://forum.hibernate.org/viewtopic.php?f=1&t=1043802&p=2490936.
Spring also had an issue for that but it seems like they forgotten to add TIMESTAMP_WITH_TIMEZONE, see https://jira.spring.io/browse/SPR-11600.

So I agree with you that if you change to TIMESTAMP_WITH_TIMEZONE it should be under a feature flag and Hibernate and Spring and probably others should also be adjusted. I think this is the best way in the end, but also difficult. I will also be happy with an adjustment that setObject with a TIMEZONE is going to support an OffSetDateTime input.

@davecramer Let me know what you guys decide. If you proceed with the TIMESTAMP_WITH_TIMEZONE change, I will test the PR you created.

[marschall]

I'm not sure we ever returned TIMESTAMP_WITH_TIMEZONE see #695

In regarding to JDBC spec compliance vs bug compatibility for frameworks and libraries: it would be helpful if the project clearly stated where it stands on this issue.
If you decide on the side of bug compatibility for frameworks and libraries then a list of frameworks and their versions is needed. Also that list of bugs should be clearly documented. In addition grepping the source code is not a good way to ensure compatibility. A better way would be to have integration tests for those frameworks and libraries. For example just because you won't find TIMESTAMP_WITH_TIMEZONE in the Spring source code doesn't mean it will break, in fact it will just end up calling #setObject which is the right thing to do
https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java#L416

Sorry if this comes off a bit ranty, the subject came up several times and I'm just seeking clarification.

As for setObject(int, OffsetDateTime, Types.TIMESTAMP) I'm not sure if supporting that is a good idea as the semantics are really unclear. Should we do silent data truncation if so to what (LocalDateTime, UTC, JVM time zone, session time zone)? Should we just ignore the the type?

[marschall]

@paulmiddelkoop as a work around have to tried to set spring.jdbc.getParameterType.ignore=true in spring.properties (see SPR-11386)?

[vlsi]

Should we do silent data truncation if so to what (LocalDateTime, UTC, JVM time zone, session time zone)? Should we just ignore the the type?

Implementation-wize, pgjdbc ALWAYS sends "timestamp with time zone formatted as string as UNKNOWN bind type" to the backend.

That is OffsetDateTime would be sent as full timestamp (year, date, time, offset), and no data truncation/conversion would take place.
Basically, Types.TIMESTAMP would be just ignored.

[paulmiddelkoop]

@marschall I tried with that property but I does not help. SimpleJdbcInsert does not use that flag.

[istibekesi]

@paulmiddelkoop Have you found any conclusion so far? How did you end up using SimpleJdbcInsert with timestamps?

[paulmiddelkoop]

@istibekesi That was a long time ago. As I remember correctly, we ended up with defining the columns ourselves with usingColumns. Maybe we also usedwithoutTableColumnMetaDataAccess to disable the meta data access.

[istibekesi]

@paulmiddelkoop Thanks, I'll take a look at this solution soon.
(My pretty ugly workaround at the moment was to use the old java.sql.Timestamp and java.time.Instant conversion back-and-forth...)

[adamkennedy]

This has also been recently been discovered to break Arrow ADBC’s JDBC-to-Arrow type conversion.

It also broke our integration of ADBC with Apache Calcite’s schemas and table definitions. Both ADBC and Calcite support timestamps with and without time zones, but all timestamps come through as the same type, which changes the functions that might be used on them and causes Calcite when doing federated query push down to generate queries which have different result types than their table types and allows the illegal comparison of incompatible types

[davecramer]

There is a PR that has been abandoned by the original author here #2715 If someone wants to push it across the finish line before I get to it that would be welcome