Hi Lukas, In fact is the same connection i pasted the main code of the function "databaseManager.startConnection();". If my supposition is true, any query using fetchSize along with fetchLazy from a pure sql code running in a Postgresql jdbc will raise this error. Sorry for not creating a complete example from this template right now because i have strongly close deadline.
Em qua, 19 de dez de 2018 às 14:54, Lukas Eder <[email protected]> escreveu: > Hi Danilo, > > Thanks - that was more of a cosmetic hint. I'm really interested in a > complete example to help reproduce this issue (e.g. derived from this > template: https://github.com/jOOQ/jOOQ-mcve). It's quite likely to be > connection pool / transaction related. Why are there two distinct > connections in your example code? Are they really distinct? Also, what's > your SQL query doing? > > Again, ideally an example that helps reproduce this issue would be good. > > Thanks, > Lukas > > On Wed, Dec 19, 2018 at 3:01 PM Danilo da Silveira Figueira < > [email protected]> wrote: > >> Hi Lukas, >> >> I've tested this and the same problem persists. >> >> ERROR: portal "C_1" does not exist >>> at org.jooq_3.11.7.POSTGRES_10.debug(Unknown Source) ~[na:na] >>> at org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.7.jar:na] >>> at >>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) >>> ~[jooq-3.11.7.jar:na] >>> at >>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1685) >>> [jooq-3.11.7.jar:na] >>> at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1636) >>> [jooq-3.11.7.jar:na] >>> at org.jooq.impl.CursorImpl.hasNext(CursorImpl.java:296) >>> [jooq-3.11.7.jar:na] >>> at joindb.JoinDatabases.loadGuiaTransitoAnimal(JoinDatabases.java:191) >>> [classes/:na] >>> at joindb.JoinDb.main(JoinDb.java:23) [classes/:na] >>> Caused by: org.postgresql.util.PSQLException: ERROR: portal "C_1" does >>> not exist >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) >>> ~[postgresql-42.2.5.jar:42.2.5] >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) >>> ~[postgresql-42.2.5.jar:42.2.5] >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2372) >>> ~[postgresql-42.2.5.jar:42.2.5] >>> at org.jooq_3.11.7.POSTGRES_10.debug(Unknown Source) >>> at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1856) >>> ~[postgresql-42.2.5.jar:42.2.5] >>> at org.jooq.impl.Tools.translate( at >>> org.jooq.tools.jdbc.DefaultResultSet.next(Tools.java:2384) >>> DefaultResultSet.java:99) ~[jooq-3.11.7.jar:na] >>> at org.jooq.impl.CursorImpl$CursorResultSet.next(CursorImpl.java:596) >>> ~[jooq-3.11.7.jar:na] >>> at >>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) >>> at >>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1659) >>> [jooq-3.11.7.jar:na] >>> ... 4 common frames omitted >>> at >>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1685) >>> at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1636) >>> at org.jooq.impl.CursorImpl.hasNext(CursorImpl.java:296) >>> at joindb.JoinDatabases.loadGuiaTransitoAnimal(JoinDatabases.java:191) >>> at joindb.JoinDb.main(JoinDb.java:23) >>> Caused by: org.postgresql.util.PSQLException: ERROR: portal "C_1" does >>> not exist >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2372) >>> at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1856) >>> at org.jooq.tools.jdbc.DefaultResultSet.next(DefaultResultSet.java:99) >>> at org.jooq.impl.CursorImpl$CursorResultSet.next(CursorImpl.java:596) >>> at >>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1659) >>> ... 4 more >>> >> >> The exact code is: >> >>> try (Cursor<Record> gtaIt = >>>> create.resultQuery(curQuery).fetchSize(2).fetchLazy()) { >>> >>> while (gtaIt.hasNext()) { >>> >>> Record gtaRow = gtaIt.fetchNext(); >>> >>> }} >> >> Thanks for the fast reply. >> >> Em qua, 19 de dez de 2018 às 11:48, Lukas Eder <[email protected]> >> escreveu: >> >>> Hi Danilo, >>> >>> I'm not sure if I understand your code example - there seem to be a few >>> relevant pieces missing. What's curQuery? And why do you first use JDBC to >>> fetch results, and then again jOOQ to fetch other results? >>> >>> Regarding your specific question, you should create a ResultQuery first >>> and call fetchSize on it, prior to calling fetchLazy(): >>> >>> try (Cursor<?> cursor : create >>> .resultQuery(sql) >>> .fetchSize(2) >>> .fetchLazy()) { >>> ... >>> } >>> >>> >>> I hope this helps, >>> Lukas >>> >>> On Wed, Dec 19, 2018 at 2:27 PM Danilo da Silveira Figueira < >>> [email protected]> wrote: >>> >>>> Hi all, >>>> >>>> I'm dealing with a big database and trying to use fetchlazy with a >>>> custom sql and it seems not working. >>>> I have two problems, first i have a custom sql, so i am could'nt find a >>>> way to use custom sql with the 'setFetchSize'. >>>> So i started a Statement and set the fetchsize in it, and passed it to >>>> the fetchLazy to return records but the function hasNext cause such error: >>>> >>>> Exception in thread "main" org.jooq.exception.DataAccessException: SQL >>>> [null]; ERROR: portal "C_1" does not exist >>>> at org.jooq_3.11.7.POSTGRES_10.debug(Unknown Source) ~[na:na] >>>> at org.jooq_3.11.7.POSTGRES_10.debug(Unknown Source) >>>> at org.jooq.impl.Tools.translate( at >>>> org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.7.jar:na] >>>> Tools.java:2384) >>>> at >>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) >>>> ~[jooq-3.11.7.jar:na] >>>> at >>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) >>>> at >>>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1685) >>>> [jooq-3.11.7.jar:na] >>>> at org.jooq.impl.CursorImpl$CursorIterator.fetchNext( at >>>> org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1685CursorImpl.java:1636) >>>> [jooq-3.11.7.jar:na] >>>> ) >>>> at org.jooq.impl.CursorImpl.hasNext(CursorImpl.java:296) >>>> [jooq-3.11.7.jar:na] >>>> at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1636) >>>> at joindb.JoinDatabases.loadGuiaTransitoAnimal(JoinDatabases.java:185) >>>> [classes/:na] >>>> at joindb.JoinDb.main(JoinDb.java:23) [classes/:na] >>>> at org.jooq.impl.CursorImpl.hasNext(CursorImpl.java:296) >>>> Caused by: org.postgresql.util.PSQLException: ERROR: portal "C_1" does >>>> not exist >>>> at joindb.JoinDatabases.loadGuiaTransitoAnimal(JoinDatabases.java:185) >>>> at joindb.JoinDb.main(JoinDb.java:23) >>>> Caused by: org.postgresql.util.PSQLException: ERROR: portal "C_1" does >>>> not exist >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) >>>> ~[postgresql-42.2.5.jar:42.2.5] >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) >>>> ~[postgresql-42.2.5.jar:42.2.5] >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2372) >>>> ~[postgresql-42.2.5.jar:42.2.5] >>>> at >>>> org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2372) >>>> at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1856) >>>> ~[postgresql-42.2.5.jar:42.2.5] >>>> at org.jooq.impl.CursorImpl$CursorResultSet.next( at >>>> org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1856) >>>> CursorImpl.java:596) ~[jooq-3.11.7.jar:na] >>>> at org.jooq.impl.CursorImpl$CursorResultSet.next( at >>>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:596) >>>> CursorImpl.java:1659) [jooq-3.11.7.jar:na] >>>> ... 4 common frames omitted >>>> at >>>> org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1659) >>>> ... 4 more >>>> >>>> The code is like this: >>>> Class.forName("org.postgresql.Driver").newInstance(); >>>> Connection conn = DriverManager.getConnection(url, USER, PASSWORD); >>>> conn.setAutoCommit(false); >>>> >>>> .... >>>> >>>> Connection connection = databaseManager.startConnection(); >>>> Statement st = connection.createStatement(); >>>> st.setFetchSize(2); >>>> DSLContext create = DSL.using(connection, SQLDialect.POSTGRES_10); >>>> >>>> ResultSet gtaResultSet = st.executeQuery("SELECT * from queryString"); >>>> >>>> int missingReference = 0; >>>> try (Cursor<Record> gtaIt = create.fetchLazy(curQuery)) >>>> {//gtaResultSet)) { >>>> // while (gtaResultSet.next()) { >>>> // Cursor<Record> gtaIt = create.fetchLazy(gtaResultSet); >>>> //some tests that doesnt works as well >>>> while (gtaIt.hasNext()) { >>>> System.out.println("Current count: " + count++); >>>> Record gtaRow = gtaIt.fetchNext(); >>>> }} >>>> >>>> >>>> Any insigths ? >>>> >>>> Thanks, >>>> Danilo >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "jOOQ User Group" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
