sqlalchemy: Result cursors are not closed when Exceptions are raised w/in ORM loading loop

Discussed in https://github.com/sqlalchemy/sqlalchemy/discussions/8709

<div type='discussions-op-text'>

^{Originally posted by briandamaged October 24, 2022} I’m fairly certain that this is a bug, but I’m mentioning it here first just in case this was a conscious design decision.

The sqlalchemy.orm.loading.instances(..) generator function implements exception handling logic that attempts to close the underlying cursor before re-raising the exception. Unfortunately, most errors will actually bypass this logic entirely. Consequently, the cursor will be garbage collected rather than closed. This can lead to unexpected behaviors depending upon the implementation of the underlying cursor.

The problem occurs because sqlalchemy.orm.loading.instances(..) is a generator function. As such, it never receives the exceptions that are raised by the Consumer. For example:

for result in sqlalchemy.orm.loading.instances(..):
  # Consumer logic
  raise ValueError("sqlalchemy.orm.loading.instances will never receive this exception!")

Instead, a GeneratorExit exception is raised inside of the generator once the generator goes out of scope. However, the GeneratorExit exception is not a subclass of Exception; therefore, it is not caught by except Exception statements such as these:

• https://github.com/sqlalchemy/sqlalchemy/blob/ee17cbad9ed4adbc1a6a1b6273ad1ded66e25d99/lib/sqlalchemy/orm/loading.py#L98
• https://github.com/sqlalchemy/sqlalchemy/blob/e3ce7ee2ee5b647416654763bd0c8c781b88d79f/lib/sqlalchemy/orm/loading.py#L86

So, the end result is that the cursor is never explicitly closed when the Consumer raises an exception. Instead, the generator goes out of scope, which causes the cursor to be garbage collected. This means that SQLAlchemy itself does not guarantee that the cursor will be closed properly.</div>