Should we auto-close ResultSet, Statement, and Connection when ResultSet is exhausted (only in safe cases)?

[rrobetti]

Hi team,
I want to discuss this idea: when a ResultSet is fully consumed, we automatically close the ResultSet, Statement, and Connection only when specific safe conditions are true (not in transaction, has not LOBs...).

Pros
Frees resources faster.
Reduces risk of connection leaks.
Can improve pool availability under load.

Cons
Changes lifecycle behavior that some clients may rely on.
Harder to debug if resources close earlier than expected.
May cause unexpected errors in edge cases.

Challenges
Defining safe conditions clearly (auto-commit, non-XA, no LOB, etc.).
Keeping behavior consistent across databases/drivers.
Preserving backward compatibility.

Examples that could break
App tries to reuse the same Statement after ResultSet ends.
App reads LOB data after ResultSet exhaustion.
Scrollable/non-forward-only ResultSet needs navigation after full read.
Multi-statement flow expects same connection/session to stay open.
Would we prefer this optimization by default, or keep current behavior and make this opt-in only?