[REACTIVE] SELECT a big amount of records then execute other queries cause hanging issue

[quantranhong1999]

Expected behavior

GIVEN SELECT all records (e.g. good amount of records e.g. 1000) from table A
THEN Execute other queries (just push the query to the event loop) would hang

Actual behavior

the next queries should not hang

Steps to reproduce the problem

Sample test to reproduce the hang issue (100% hang):

    @Test
    void selectABigRecordsThenExecuteOtherQueriesShouldNotHang() {
        // Setup DSLContext
        Settings jooqSettings = new Settings()
            .withRenderFormatted(true)
            .withStatementType(StatementType.PREPARED_STATEMENT);
        Connection r2dbcConnection = postgresExtension.getConnection().block();
        DSLContext dslContext = DSL.using(r2dbcConnection, SQLDialect.POSTGRES, jooqSettings);

        // Create `users` table
        Table<Record> USER_TABLE = DSL.table("users");
        Field<String> USERNAME = DSL.field("username", SQLDataType.VARCHAR(255).notNull());
        Field<String> PASSWORD = DSL.field("password", SQLDataType.VARCHAR);

        Mono.from(dslContext.createTableIfNotExists(USER_TABLE)
                .column(USERNAME)
                .column(PASSWORD)
                .constraints(DSL.primaryKey(USERNAME)))
            .block();

        // Insert 1000 users
        Flux.range(1, 1000)
            .flatMap(counter -> Mono.from(dslContext.insertInto(USER_TABLE)
                .set(USERNAME, counter.toString())
                .set(PASSWORD, "password")))
            .collectList()
            .block();

        // Create `mailboxes` table
        Table<Record> MAILBOX_TABLE = DSL.table("mailboxes");
        Field<String> MAILBOX_ID = DSL.field("mailbox_id", SQLDataType.VARCHAR(255).notNull());

        Mono.from(dslContext.createTableIfNotExists(MAILBOX_TABLE)
                .column(MAILBOX_ID)
                .constraints(DSL.primaryKey(MAILBOX_ID)))
            .block();

        // insert 1 dummy record into mailboxes table
        Mono.from(dslContext.insertInto(MAILBOX_TABLE)
                .set(MAILBOX_ID, "whatever"))
            .block();

        System.out.println("finished provisioning data");

        // list all the users then query in the same `users` table as well -> this would hang forever
        assertThat(Flux.from(dslContext.selectFrom(USER_TABLE))
            .map(record -> record.get(USERNAME))
            .flatMap(username -> Mono.from(dslContext.selectFrom(USER_TABLE)
                .where(USERNAME.eq(username), PASSWORD.eq("password"))),
                1)
            .collectList()
            .block())
            .hasSize(1000);

        // list all the users then query another `mailboxes` table -> this would hang forever
        assertThat(Flux.from(dslContext.selectFrom(USER_TABLE))
            .map(record -> record.get(USERNAME))
            .concatMap(any -> Mono.from(dslContext.selectOne()
                    .from(MAILBOX_TABLE)))
            .collectList()
            .block())
            .hasSize(1000);
    }

Notes:

• if initially SELECT not big e.g. 1000 records but just a few records e.g. 5, then the following queries would not hang.
• adding .collectList().flatMapIterable(Function.identity()) to the initial SELECT would avoid hanging

jOOQ Version

3.19.6

Database product and version

PostgreSQL 16.1

Java Version

java 21

JDBC / R2DBC driver name and version (include name if unofficial driver)

r2dbc-postgresql 1.0.4.RELEASE

[lukaseder]

Thanks for your report. Is it really different from this one?

• [REACTIVE] DELETE with RETURNING query + Flux + concatMap cause hanging issue #16055

I.e. you're using concatMap() as in the other issue.

[quantranhong1999]

Thanks for your report. Is it really different from this one?

Hi, this is a different issue.

On the other one, it only happens when doing a specific query (DELETE with RETURNING) + concatMap. And always happens regardless of record size.

In this one, it happens regardless of concatMap or flatMap, and only happens when the record size is good enough.