Thread: TimeZone issue with 42.2.1
Hi, we're seeing an issue retrieving a field with a timezone in 42.2.1. The same software and table works with 42.2.0.
The schema is:
snapteam=# \d snapteam1.table3 Table "snapteam1.table3"Column | Type | Modifiers --------+------------------------+-----------col1 | time(6) with time zone | col2 | time with time zone[] | col3 | time without time zone |
The data is:
snapteam=# select * from snapteam1.table3; col1 | col2 | col3
----------------+------------------+------12:00:00-08 | | 13:00:00-08 | | 24:00:00-14:59 | | 24:00:00-14:59 | {24:00:00-14:59} |
(4 rows)The error is:
Can not resolve type id for sun.util.calendar.ZoneInfo (using com.snaplogic.document.parser.plugin.DocumentTypeResolverBuilder$DataTypeIdResolver) (through reference chain: java.util.LinkedHashMap["col2"] ->org.postgresql.jdbc.PgArray["resultSet"] ->org.postgresql.jdbc.PgResultSet["statement"] ->org.postgresql.jdbc.PgStatement["connection"] ->org.postgresql.jdbc.PgConnection["queryExecutor"] ->org.postgresql.core.v3.QueryExecutorImpl["timeZone"])
This is occurring on a 9.2 server. I haven't tracked down to the individual line that's causing the problem - I wanted to zero in on when it broke first since a problem that started 6 months ago is more likely to be in our code than in a problem that started in the last few weeks.
Any ideas?
Thanks,
Bear
>an not resolve type id for sun.util.calendar.ZoneInfo
This sounds like a Jackson error.
The column is <<time with time zone[]>>, so it is represented as PgArray object.
As Jackson serializes PgArray, it treats whatever getter it finds as a property.
It runs into PgArray#getResultSet and thinks it is a getter for resultSet property. Of course that causes it to serialize underlying statement, connection, etc.
Finally it ends up with serializing QueryExecutorImpl.timeZone field, and it fails so.
One of the recommendations might include "avoid Jackson-serializing random objects" =)
You might have better luck if you serialize PgArray manually or provide a Jackson mixin annotation (see https://github.com/FasterXML/jackson-docs/wiki/JacksonMixInAnnotations ). Technically speaking, PgArray#getArray might be just enough.
You might ask "why did it work before 42.2.1?", and it turns out the trigger line is https://github.com/pgjdbc/pgjdbc/commit/a94cfeace5d66b4fe8d8fa3b16986baebaec2a11#diff-0ac94407005dd81cdbe1efa6066de013R2624
The thing is backend sends connection timezone at connection setup phase, and pgjdbc 42.2.0 ignored that. pgjdbc 42.2.1 parses timezone message in the initial parameter status messages, thus it ends up with non-null timezone, thus it fails your app.
Vladimir
Thanks!
This explanation goes a long way towards explaining a different issue we've had with inserting data into arrays. We use JSON for internal communications and have been seeing some weirdness with it as well. The details are different but it's another place where we're using Jackson to serialize and deserialize something. I've seen messages with both "unable to convert java.util.ArrayList to java.sql.Array" and references to PgArray.
Bear
On Mon, Mar 5, 2018 at 3:23 PM, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
>an not resolve type id for sun.util.calendar.ZoneInfoThis sounds like a Jackson error.It looks like you are trying to Jackson-serialize the result of col2 column.The column is <<time with time zone[]>>, so it is represented as PgArray object.As Jackson serializes PgArray, it treats whatever getter it finds as a property.It runs into PgArray#getResultSet and thinks it is a getter for resultSet property. Of course that causes it to serialize underlying statement, connection, etc.Finally it ends up with serializing QueryExecutorImpl.timeZone field, and it fails so.One of the recommendations might include "avoid Jackson-serializing random objects" =)You might have better luck if you serialize PgArray manually or provide a Jackson mixin annotation (see https://github.com/FasterXML/jackson-docs/wiki/ JacksonMixInAnnotations ). Technically speaking, PgArray#getArray might be just enough. You might ask "why did it work before 42.2.1?", and it turns out the trigger line is https://github.com/pgjdbc/pgjdbc/commit/ a94cfeace5d66b4fe8d8fa3b16986b aebaec2a11#diff- 0ac94407005dd81cdbe1efa6066de0 13R2624 The thing is backend sends connection timezone at connection setup phase, and pgjdbc 42.2.0 ignored that. pgjdbc 42.2.1 parses timezone message in the initial parameter status messages, thus it ends up with non-null timezone, thus it fails your app.Vladimir