Any way to prevent jooq from auto-closing cursors (etc)?

[Steve Ash]

Quick question:

Is there any way to suppress the "auto-close" behavior of jooq's Cursor?

We use spring's @Transactional and I have a situation where I want to open a cursor in a transaction bound to threadA, pass that cursor to another thread (threadB) to read everything off, then pass it back to threadA and the transaction will complete.  Everything is published safely and nothing is processed concurrently.  However, when threadB gets to the end, jooq decides to close the Cursor.  This is unfortunate for us, because we use Spring managed transactions via TransactionAwareDataSourceProxy.  When threadB closes the connection, the TADSP looks up in its thread local, can't find anything for that data source, and assumes that this was a connection that wasn't bound to a transaction synchronization -- so it calls close() on it for reals.  Then go back to threadA who doesn't know that his bound connection has been close()d out from under him, and we when threadA tries to commit, we get a nasty exception from c3p0 warning us to not work on an already closed connection.

I realize that this whole business of passing a connection to another thread is suspect -- and I'm working on not doing that -- but I still think it's a valid question: can we control this auto-close behavior?  Probably per Cursor instance?  I think the friendly behavior probably does the right thing in almost every case...except this one.

Steve

[Lukas Eder]

Hi Steve,

Very interesting question. The main reason why we're "auto-closing" cursors is to ensure that ExecuteListener events are fired at the right moment and with all use-cases of using lazy fetching, including using the Cursor as an Iterator. The relevant GitHub issues are referenced from the call to close() within org.jooq.impl.CursorImpl.CursorIterator:

            // [#1868] [#2373] [#2385] This calls through to Utils.safeClose()

            // if necessary, lazy-terminating the ExecuteListener lifecycle if

            // the result is not eager-fetched.

            if (record == null) {

                CursorImpl.this.close();

            }

Changing this behaviour is an option, of course - at least in a major release.

In a minor release, we might introduce a new flag on ResultQuery, similar to the existing Query.keepStatement() flag (which keeps open PreparedStatements). A ResultQuery.keepResultSet() flag, for instance.

Right now, there is an option for you to work around this issue. You can inject a proxy ResultSet that ignores the above close() call. You can do this by implementing an ExecuteListener's executeEnd() method, and setting the proxy on the ExecuteContext.resultSet() method. The AbstractResultQuery.execute() method shows how this works:

        listener.executeStart(ctx);

        if (ctx.statement().execute()) {

            ctx.resultSet(ctx.statement().getResultSet());

        }

        listener.executeEnd(ctx);

        // Fetch a single result set

        if (!many) {

            if (ctx.resultSet() != null) {

                Field<?>[] fields = getFields(ctx.resultSet().getMetaData());

                cursor = new CursorImpl<R>(ctx, listener, fields, intern.internIndexes(fields), keepStatement(), keepResultSet(), getRecordType());

                if (!lazy) {

                    result = cursor.fetch();

                    cursor = null;

                }

            }

            else {

                result = new ResultImpl<R>(ctx.configuration());

            }

        }

Let me know if that's a viable option for you, for the time being.

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Steve Ash]

Thanks for the info.  For this particular instance I just reworked my code to avoid sharing the connection across threads, but I'll keep this in mind in the future.  In any case, I would vote for a keepResultSet() option to suppress the actual closing as needed.

[Lukas Eder]

Hi Steve,

Thanks for the feedback. I think that the keepResultSet() method would certainly be useful. In particular, if users make use of any of

- ResultQuery.resultSetConcurrency()

- ResultQuery.resultSetHoldability()

- ResultQuery.resultSetType()

... then auto-closing doesn't make a lot less sense. Perhaps, there should even be new API in the future that allows for rewinding a Cursor, something that is currently not possible via the jOOQ API.

I've created the following issues:

Adding keepResultSet():

https://github.com/jOOQ/jOOQ/issues/4199

Allowing to use scrollable Cursors:

https://github.com/jOOQ/jOOQ/issues/4200

Best Regards,

Lukas