Use attempt_to_set_autocommit everywhere.

[clokep]

This is pulled from my psycopg3 branch, it uses attempt_to_set_autocommit instead of an assert isinstance(conn, psycopg2.extensions.connection) followed by directly setting the autocommit.

tl;dr we're just being a bit more generic.

[clokep]

This change is weirdly so that mypy treats db_engine as a BaseDatabaseEngine and not a PostgresEngine or it dislikes that db_conn is a Connection (protocol) instead of psycopg2.extensions.connection.

[DMRobertson]

Probably related to

synapse/synapse/storage/engines/postgres.py

Lines 34 to 36 in 4f1840a

	class PostgresEngine(
	BaseDatabaseEngine[psycopg2.extensions.connection, psycopg2.extensions.cursor]
	):

which was my way of trying to teach mypy which connection and cursor type to expect when we're using the PostgresEngine.

[DMRobertson]

(Although if we had to do the type-assert on line -1042 then it clearly wasn't working.)

[clokep]

Right -- from those mypy is able to deduce that the PostgresEngine creates a psycopg2.extensions.connection; but the API expects a Connection protocol, and it doesn't seem to understand that psycopg2.extensions.connection matches Connection?

I wonder if we should make LoggingTransaction etc. generic and be passing these through so that the type checking knows the real types of things? I guess you still need a generic protocol type though for use with the BaseDatabaseEngine class.

[DMRobertson]

it doesn't seem to understand that psycopg2.extensions.connection matches Connection?

That's a surprise to me:

2023-11-09 21:04:54 ✔  $ cat temp.py
import psycopg2.extensions
import typing
import synapse.storage.types

pg_conn: psycopg2.extensions.connection
generic_conn: synapse.storage.types.Connection

generic_conn = pg_conn


dmr on titan in synapse on  develop is 📦 v1.96.0rc1 via 🐍 v3.11.6 (matrix-synapse-py3.11) via 🦀 v1.68.0 
2023-11-09 21:05:01 ✔  $ mypy temp.py 
Success: no issues found in 1 source file

[clokep]

tests/server.py:1085: error: Argument 1 to "attempt_to_set_autocommit" of "PostgresEngine" has incompatible type "Connection"; expected "connection"  [arg-type]

More than happy to do another PR changing it back though?

[DMRobertson]

🤷 No strong opinions here!