AWS Postgres:The JDBC storage plugin failed while trying setup the SQL query

1

I countered this error when I tried to access a materialized view in PostgreSQL.
step 1:
I create a VDS from this materialized view as "Super Cloud"."67572"."device_view".I can query below SQL successfully.
SELECT “serialnumber”,“pppusername”,“ipaddress”,TO_TIMESTAMP(“lastinformtime”/1000) AS lastinformtime
FROM “RDS Cloud”.public.device_view
WHERE “orgid”=‘67572’
AND pppusername <> ‘’

step 2:
I do further query based on the above VDS created.

SELECT r.* FROM “Super Cloud”.“67572”.device_view r INNER JOIN
(
SELECT pppusername,max(lastinformtime) as latestInform
FROM “Super Cloud”.“67572”.device_view group by pppusername
) li ON li.pppusername=r.pppusername AND li.latestInform=r.lastinformtime

And I encountered errors in the step 2:

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

sql SELECT *
FROM “public”.“device_view”
WHERE (“orgid” COLLATE “C”) = (‘67572’ COLLATE “C”)AND (“pppusername” COLLATE “C”) <> (‘’ COLLATE “C”)
plugin RDS Cloud
SqlOperatorImpl JDBC_SUB_SCAN
Location 4:0:3
SqlOperatorImpl JDBC_SUB_SCAN
Location 4:0:3
Fragment 4:0

[Error Id: e9e306d9-5ba2-41ed-86bb-7ddbd8165089 on ip-10-2-41-53.us-west-2.compute.internal:31010]

(org.postgresql.util.PSQLException) FATAL: password authentication failed for user “calixcloud”
org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication():438
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl():222
org.postgresql.core.ConnectionFactory.openConnection():49
org.postgresql.jdbc.PgConnection.():194
org.postgresql.Driver.makeConnection():450
org.postgresql.Driver.connect():252
org.apache.commons.dbcp2.DriverConnectionFactory.createConnection():38
org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject():260
org.apache.commons.pool2.impl.GenericObjectPool.create():889
org.apache.commons.pool2.impl.GenericObjectPool.borrowObject():433
org.apache.commons.pool2.impl.GenericObjectPool.borrowObject():362
org.apache.commons.dbcp2.PoolingDataSource.getConnection():134
org.apache.commons.dbcp2.BasicDataSource.getConnection():1533
com.dremio.exec.store.jdbc.CloseableDataSource$DatasourceWrapper.getConnection():80
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():155
com.dremio.exec.store.CoercionReader.setup():109
com.dremio.sabot.op.scan.ScanOperator$1.run():189
com.dremio.sabot.op.scan.ScanOperator$1.run():185
java.security.AccessController.doPrivileged():-2
javax.security.auth.Subject.doAs():422
org.apache.hadoop.security.UserGroupInformation.doAs():1836
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():185
com.dremio.sabot.op.scan.ScanOperator.setupReader():177
com.dremio.sabot.op.scan.ScanOperator.setup():163
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():560
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():530
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.Pipeline.setup():58
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():353
com.dremio.sabot.exec.fragment.FragmentExecutor.run():243
com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():91
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():600
com.dremio.sabot.task.AsyncTaskWrapper.run():103
com.dremio.sabot.task.slicing.SlicingThread.run():110

sql SELECT *
FROM “public”.“device_view”
WHERE (“orgid” COLLATE “C”) = (‘67572’ COLLATE “C”)AND (“pppusername” COLLATE “C”) <> (‘’ COLLATE “C”)
plugin RDS Cloud
SqlOperatorImpl JDBC_SUB_SCAN
Location 4:0:3
SqlOperatorImpl JDBC_SUB_SCAN
Location 4:0:3
Fragment 4:0

org.postgresql.core.v3.ConnectionFactoryImpl(ConnectionFactoryImpl.java:438)
org.postgresql.core.v3.ConnectionFactoryImpl(ConnectionFactoryImpl.java:222)
org.postgresql.core.ConnectionFactory(ConnectionFactory.java:49)
org.postgresql.jdbc.PgConnection(PgConnection.java:194)
org.postgresql.Driver(Driver.java:450)
org.postgresql.Driver(Driver.java:252)
org.apache.commons.dbcp2.DriverConnectionFactory(DriverConnectionFactory.java:38)
org.apache.commons.dbcp2.PoolableConnectionFactory(PoolableConnectionFactory.java:260)
org.apache.commons.pool2.impl.GenericObjectPool(GenericObjectPool.java:889)
org.apache.commons.pool2.impl.GenericObjectPool(GenericObjectPool.java:433)
org.apache.commons.pool2.impl.GenericObjectPool(GenericObjectPool.java:362)
org.apache.commons.dbcp2.PoolingDataSource(PoolingDataSource.java:134)
org.apache.commons.dbcp2.BasicDataSource(BasicDataSource.java:1533)
com.dremio.exec.store.jdbc.CloseableDataSource$DatasourceWrapper(CloseableDataSource.java:80)
com.dremio.exec.store.jdbc.JdbcRecordReader(JdbcRecordReader.java:155)
com.dremio.exec.store.CoercionReader(CoercionReader.java:109)
com.dremio.sabot.op.scan.ScanOperator$1(ScanOperator.java:189)
com.dremio.sabot.op.scan.ScanOperator$1(ScanOperator.java:185)
…(:0)
org.apache.hadoop.security.UserGroupInformation(UserGroupInformation.java:1836)
com.dremio.sabot.op.scan.ScanOperator(ScanOperator.java:185)
com.dremio.sabot.op.scan.ScanOperator(ScanOperator.java:177)
com.dremio.sabot.op.scan.ScanOperator(ScanOperator.java:163)
com.dremio.sabot.driver.SmartOp$SmartProducer(SmartOp.java:560)
com.dremio.sabot.driver.Pipe$SetupVisitor(Pipe.java:79)
com.dremio.sabot.driver.Pipe$SetupVisitor(Pipe.java:63)
com.dremio.sabot.driver.SmartOp$SmartProducer(SmartOp.java:530)
com.dremio.sabot.driver.StraightPipe(StraightPipe.java:102)
com.dremio.sabot.driver.StraightPipe(StraightPipe.java:102)
com.dremio.sabot.driver.StraightPipe(StraightPipe.java:102)
com.dremio.sabot.driver.Pipeline(Pipeline.java:58)
com.dremio.sabot.exec.fragment.FragmentExecutor(FragmentExecutor.java:353)
com.dremio.sabot.exec.fragment.FragmentExecutor(FragmentExecutor.java:243)
com.dremio.sabot.exec.fragment.FragmentExecutor(FragmentExecutor.java:91)
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl(FragmentExecutor.java:600)
com.dremio.sabot.task.AsyncTaskWrapper(AsyncTaskWrapper.java:103)
com.dremio.sabot.task.slicing.SlicingThread(SlicingThread.java:110)

step 3:
The funny thing is if I replace the “INNER JOIN” with “LEFT JOIN” in the SQL, the SQL was executed without errors.

Could anyone give me some clues?Thanks.And the profile of the query is provided.
56e7cb8f-33e2-4e78-a5e8-ebd6576d54fc.zip (11.4 KB)

2

@Isabella_Zong I see you are running a very old version of Dremio 3.0.6-201812082352540436-1f684f9, if my memory is right (This is almost 4 years back), I think there was a bug around this. even if this is a bug, we cannot fix this due to the version number

Can you please try this query on a version >= 21.x?

3

Thanks for your quick attention to this!
I recreated RDS connection and modified the value of “The Record Fetch Size” option from 10 to 0 according to Dremio documentation.The query can work now.But I’m not sure whether this operation may bring any side efforts.

4

@Isabella_Zong This should be a good workaround, decreasing fetch size may have a slight impact on the performance but since at 10 the query fails, this should be fine

Are we sure there were no other changes that happened along with reducing fetch size?