diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/7930.rst | 7 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/_psycopg_common.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg.py | 8 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_dialect.py | 17 |
4 files changed, 34 insertions, 6 deletions
diff --git a/doc/build/changelog/unreleased_14/7930.rst b/doc/build/changelog/unreleased_14/7930.rst new file mode 100644 index 000000000..bf4f9988c --- /dev/null +++ b/doc/build/changelog/unreleased_14/7930.rst @@ -0,0 +1,7 @@ +.. change:: + :tags: bug, postgresql + :tickets: 7930 + + Fixed an issue what would cause autocommit mode to be reset + when using pre_ping in conjunction engine level autocommit + on the psycopg2 driver. diff --git a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py index 7f936fefb..e7d5e77c3 100644 --- a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py +++ b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py @@ -170,15 +170,17 @@ class _PGDialect_common_psycopg(PGDialect): def do_ping(self, dbapi_connection): cursor = None + before_autocommit = dbapi_connection.autocommit try: - self._do_autocommit(dbapi_connection, True) + if not before_autocommit: + self._do_autocommit(dbapi_connection, True) cursor = dbapi_connection.cursor() try: cursor.execute(self._dialect_specific_select_one) finally: cursor.close() - if not dbapi_connection.closed: - self._do_autocommit(dbapi_connection, False) + if not before_autocommit and not dbapi_connection.closed: + self._do_autocommit(dbapi_connection, before_autocommit) except self.dbapi.Error as err: if self.is_disconnect(err, dbapi_connection, cursor): return False diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py index 9207221df..b811d1cab 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py @@ -385,12 +385,14 @@ class PGDialect_psycopg(_PGDialect_common_psycopg): != self._psycopg_TransactionStatus.IDLE ): dbapi_conn.rollback() - before = dbapi_conn.autocommit + before_autocommit = dbapi_conn.autocommit try: - self._do_autocommit(dbapi_conn, True) + if not before_autocommit: + self._do_autocommit(dbapi_conn, True) dbapi_conn.execute(command) finally: - self._do_autocommit(dbapi_conn, before) + if not before_autocommit: + self._do_autocommit(dbapi_conn, before_autocommit) def do_rollback_twophase( self, connection, xid, is_prepared=True, recover=False diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index fb3a522fc..0093eb5ba 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -1068,6 +1068,23 @@ class MiscBackendTest( dbapi_conn.rollback() eq_(val, "off") + @testing.combinations((True,), (False,), argnames="autocommit") + def test_autocommit_pre_ping(self, testing_engine, autocommit): + engine = testing_engine( + options={ + "isolation_level": "AUTOCOMMIT" + if autocommit + else "SERIALIZABLE", + "pool_pre_ping": True, + } + ) + for i in range(4): + with engine.connect() as conn: + conn.execute(text("select 1")).scalar() + + dbapi_conn = conn.connection.dbapi_connection + eq_(dbapi_conn.autocommit, autocommit) + def test_deferrable_flag_engine(self): engine = engines.testing_engine( options={ |