Error on querying Postgresql 11 related to timestamp values

1

All our datawarehouse tables contain a couple of timestamp columns and a json type column. When querying these tables in 3.1.8 or 3.1.10 on Centos 7 Linux, we get an error in the logs as below.
The same query on the same database server from Windows dremio 3.0 works.

The value the error complains about, {1}, seems to come from a different column than the timestamp columns. Perhaps Dremio or JDBC is looking at the wrong column due to skipping the json-typed column?

We already tried whether upgrading the PostgreSQL JDBC driver would help, hence the log lists version 42.2.5 instead of the provided 42.1.1.

The server log spits out:
2019-04-26 11:59:12,191 [233d2990-e984-d5eb-1403-245a31a27e00:foreman] INFO c.d.s.fabric.FabricConnectionManager - [FABRIC]: No connection active, opening new connection to ensdw.vredestein.com:45678.
2019-04-26 11:59:12,308 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 233d2990-e984-d5eb-1403-245a31a27e00:0:0
2019-04-26 11:59:12,337 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 233d2990-e984-d5eb-1403-245a31a27e00:1:0
2019-04-26 11:59:12,715 [e1 - 233d2990-e984-d5eb-1403-245a31a27e00:frag:1:0] INFO c.d.exec.store.jdbc.JdbcRecordReader - User Error Occurred [ErrorId: 0e99442a-0f4f-44f8-995c-c66d8271d936]
com.dremio.common.exceptions.UserException: Failure while attempting to read from database.
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:746) ~[dremio-common-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:250) [dremio-extra-plugin-jdbc-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.exec.store.CoercionReader.next(CoercionReader.java:212) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.op.scan.ScanOperator.outputData(ScanOperator.java:215) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.driver.SmartOp$SmartProducer.outputData(SmartOp.java:526) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.driver.StraightPipe.pump(StraightPipe.java:56) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.driver.Pipeline.doPump(Pipeline.java:109) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.driver.Pipeline.pumpOnce(Pipeline.java:99) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run(FragmentExecutor.java:311) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run(FragmentExecutor.java:307) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_201]
at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_201]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1836) [hadoop-common-2.8.3.jar:na]
at com.dremio.sabot.exec.fragment.FragmentExecutor.run(FragmentExecutor.java:264) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.exec.fragment.FragmentExecutor.access$1000(FragmentExecutor.java:93) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run(FragmentExecutor.java:619) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.task.AsyncTaskWrapper.run(AsyncTaskWrapper.java:103) [dremio-sabot-kernel-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop(SlicingThread.java:123) [dremio-extra-sabot-scheduler-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.sabot.task.slicing.SlicingThread.run(SlicingThread.java:68) [dremio-extra-sabot-scheduler-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
Caused by: org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}
at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:365) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.TimestampUtils.toTimestamp(TimestampUtils.java:396) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.TimestampUtils.toDate(TimestampUtils.java:520) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgResultSet.getDate(PgResultSet.java:498) ~[postgresql-42.2.5.jar:42.2.5]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:682) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:682) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at com.dremio.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:540) ~[dremio-extra-plugin-jdbc-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
at com.dremio.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:240) [dremio-extra-plugin-jdbc-3.1.10-201904162146020182-adf690d.jar:3.1.10-201904162146020182-adf690d]
… 17 common frames omitted
Caused by: java.lang.NumberFormatException: Timestamp has neither date nor time
at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:360) ~[postgresql-42.2.5.jar:42.2.5]
… 24 common frames omitted
2019-04-26 11:59:12,772 [e0 - 233d2990-e984-d5eb-1403-245a31a27e00:frag:0:0] INFO c.d.s.e.f.FragmentStatusReporter - 233d2990-e984-d5eb-1403-245a31a27e00:0:0: State to report: CANCELLED

2

Hi @alban

I am able to reproduce something very similar. Would you mind sending me your profile for the failed job. I want to see what we push down into Postgres

Share A Query Profile

Thanks
@balaji.ramaswamy

3

It does look like the query is passed 1:1 to Postgres. Perhaps this is an issue with the timezone setting of the client (Dremio)?

Profile attached (anonimized manually)7b434977-d283-4754-b34b-5a30ef7fa711-anonimized.zip (9.5 KB)

4

Hi @alban

It does look like the same behavior I reproduced. As we look to see how we can fix this, you can try couple of workarounds

  1. Currently we do not support the JSON column so you can try individually select the columns
  2. Recreate the Postges table with the JSON column as the last column and then select * should work

Thanks
@balaji.ramaswamy

5

Hi guys, are there still issues with accessing Postgres tables on the newest version?
I have connected up 2 databases, both hosted on AWS, and only a few of the tables allow me to browse. The rest say “Failure while attempting to read from database”.

I managed to build a basic query across one of the failing tables, but when I open the available fields list there are a lot missing, which I assume has something to do with their “type”?

6

Hi @gdelaney

Do you have JSON columns?

7

Hi Balaji,

We do on a few tables but not all.

Also having issues access elastic clusters.

Thanks,

Greg

8

The problem is that the interface, by default, does select *. We do not have a choice in that, it happens before we can select columns to put into a user-facing table definition in Dremio.

The other option, moving the JSON columns to the end, will be difficult to implement for us and impossible to maintain; The JSON column is part of a set of default columns that our ETL process creates on creating the table (it is used to track corrections for data-quality), after which the columns from the source-table are appended. The reason for this order of operation is that it puts the default columns in a predictable position; if the source table sprouts new columns, those can only be appended to the end of the table.

Also, reordering columns requires rewriting the table. Some of our tables are several GBs in size. We’d have to do that every time a new column gets added from the source to put the JSON column at the end of the list again…

It’s not a huge problem that Dremio errors at this phase of table definition, the interface still works and these columns are not meant for users anyway, but it looks like a fairly simple to fix off-by-one error when accessing column contents after skipping over those of types that Dremio doesn’t support.

9

@gdelaney

Please open a new community post for the elastic issue

Thanks
@balaji.ramaswamy