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

**URL:** <https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832>\
**Category:** Uncategorized\
**Created:** [July 26, 2023, 5:12am UTC](https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832 "2023-07-26T05:12:08Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Isabella\_Zong](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/isabella_zong/32/386_2.png) [@Isabella\_Zong](https://community.dremio.com/u/Isabella_Zong)\
**Post date:** [July 26, 2023, 5:12am UTC](https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832/1 "2023-07-26T05:12:08Z")

</div>

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](https://community.dremio.com/uploads/short-url/uIGmiO72AklO2KJgotdIrGzAMJW.zip) (11.4 KB)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [July 27, 2023, 8:26pm UTC](https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832/2 "2023-07-27T20:26:29Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![Isabella\_Zong](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/isabella_zong/32/386_2.png) [@Isabella\_Zong](https://community.dremio.com/u/Isabella_Zong)\
**Post date:** [July 28, 2023, 2:40am UTC](https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832/3 "2023-07-28T02:40:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [July 31, 2023, 4:21am UTC](https://community.dremio.com/t/aws-postgres-the-jdbc-storage-plugin-failed-while-trying-setup-the-sql-query/10832/4 "2023-07-31T04:21:38Z")

</div>

@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?