Operating Environment
org.postgresql:postgresql:9.3-1102-jdbc41 org.mybatis:mybatis:3.2.7
Connecting to RedShift to retrieve data
SELECT* FROM sample_table WHERE
<foreach item="item" collection="lists" open="("close=")"separator="OR">
(xxxBETWEEN#{item.From}AND#{item.To})
</foreach>
When the list of parameters to be set to mybatis is about 3000 or more in size, the extraction fails.If the size is small, it can be extracted as required.
2015-11-07 12:46:26,672 [main] ERROR Main-org.apache.ibatis.exception.PersistenceException:
### Error querying database.Cause: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
### The error may exist in com/xxx/sql/sql_redshift.xml
### The error may involve com.xxx.sql.findXXs-Inline
### The error occurred while setting parameters
It looks like an IO error with a query being too long. Is there any good way?
java sql amazon-redshift
You can increase the max_stack_depth value in the postgresql configuration for the time being.(Default value is 2,048kB)
However, I don't recommend queries with super-large IN clauses because they have poor performance and if SQL is too long, Java side (Mybatis) may have an OutOfMemory Error.
I think it would be better to review the extraction conditions or divide them into multiple queries and combine the results on the Java side.
If the list is about 3000...Wouldn't it get stuck in the length limit of SQL itself or the limit of SQL parser?
What happens if I just run the SQL generated there on pgadmin?
Looking at the page below, it seems that if you run SQL with 10,000 NOTs connected, you will get in trouble, and is there a possibility that Postgres is limited rather than mybatis limit?
http://postgresql.g.hatena.ne.jp/umitanuki/20090220/1235094391
© 2024 OneMinuteCode. All rights reserved.