[CT-3020] More defensive code around fetchmany in SQLConnectionManager.get_result_from_cursor

[jtcohen6]

Housekeeping

• I am a maintainer of dbt-core

Short description

In #7545, we changed dbt show --limit to apply the limit while fetching the result set from the database cursor, instead of after the full result set was already fetched and loaded into memory.

dbt-core/core/dbt/adapters/sql/connections.py

Lines 126 to 127 in 582faa1

	if limit:
	rows = cursor.fetchmany(limit)

However, multiple adapter maintainers weren't aware that they needed to add a fetchmany method, leading to issues in some externally maintained adapters upon upgrading to v1.6:

• Implement fetchmany starburstdata/dbt-trino#341
• dbt show fails with 'DatabricksSQLCursorWrapper' object has no attribute 'fetchmany' databricks/dbt-databricks#408

Instead of raising an ugly error ('DatabricksSQLCursorWrapper' object has no attribute 'fetchmany'), we should:

• Add some defensive code, to first verify the existence of a fetchmany method
• If no such method is supported for this adapter/platform's cursor, raise a warning, and apply the limit after fetching the result instead (the previous behavior from v1.5)

Acceptance criteria

• It is possible to run dbt show --limit 10 on an adapter that inherits from the "SQL adapter," and does not support a fetchmany method. The final result set should still be limited to 10. (I imagine we'd want a unit test to mock this scenario.)
• We have added a test case to dbt-tests-adapter for dbt show --limit. Adapter maintainers know to inherit & use this test case when validating their upgrade to v1.6. We have reflected the need for a fetchmany method in the v1.6 adapter maintainer guide: Adapter Maintainers: Upgrading to dbt-core `1.6.0` #7958.

Impact to Other Teams

Adapters

Will backports be required?

1.6.latest

Context

No response

[martynydbt]

Related: #8496 if we don't continue with fetchmany we shouldn't do this ticket.

[jtcohen6]

There may be some benefit to continuing to use fetchmany, in addition to templating the --limit into the query. That's an implementation detail for whomever picks up #8496.

If we continue to call fetchmany at all, we need more defensive code wrapping it, for adapters that have not implemented the method (this ticket).

If we stop calling fetchmany entirely, then this issue can be closed as "not planned."

[martynydbt]

@MichelleArk is this ticket still needed?