From 86f6409d883e9bed5174c521ecaac49ab0c0474d Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Mon, 11 Apr 2022 23:19:16 +0200 Subject: [PATCH] Fix psycopg2 pre_ping with autocommit Fixed an issue what would cause autocommit mode to be reset when using pre_ping in conjunction engine level autocommit on the psycopg2 driver. Fixes: #7930 Change-Id: I4cccaf1b7f8cbacd853689458080784114fcc390 (cherry picked from commit 363b68e08e9ceed4ce6821f5fd48ab32bdfd807c) --- doc/build/changelog/unreleased_14/7930.rst | 7 +++++++ lib/sqlalchemy/dialects/postgresql/psycopg2.py | 8 +++++--- test/dialect/postgresql/test_dialect.py | 17 +++++++++++++++++ 3 files changed, 29 insertions(+), 3 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/7930.rst diff --git a/doc/build/changelog/unreleased_14/7930.rst b/doc/build/changelog/unreleased_14/7930.rst new file mode 100644 index 0000000000..bf4f9988ca --- /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/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index adebc9b676..f7121a82a1 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -835,15 +835,17 @@ class PGDialect_psycopg2(PGDialect): def do_ping(self, dbapi_connection): cursor = None + before_autocommit = dbapi_connection.autocommit try: - dbapi_connection.autocommit = True + if not before_autocommit: + dbapi_connection.autocommit = True cursor = dbapi_connection.cursor() try: cursor.execute(self._dialect_specific_select_one) finally: cursor.close() - if not dbapi_connection.closed: - dbapi_connection.autocommit = False + if not before_autocommit and not dbapi_connection.closed: + dbapi_connection.autocommit = before_autocommit except self.dbapi.Error as err: if self.is_disconnect(err, dbapi_connection, cursor): return False diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index 1d797a697d..8aa9036495 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -1095,6 +1095,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={ -- 2.47.2