pals
pals copied to clipboard
level12
Incompatibility with SQLAlchemy's pool_pre_ping
I just realized that PALS is currently incompatible with setting pool_pre_ping=True on the used engine.
Example code:
import sqlalchemy as sa
import pals
engine = sa.create_engine(
"postgresql+psycopg://user:pw@localhost:123/somedb",
pool_pre_ping=True,
)
locker = pals.Locker(app_name="foo", create_engine_callable=lambda: engine)
lock1 = locker.lock("my-lock")
lock1.acquire()
lock1.release()
lock1 = locker.lock("my-lock")
# Error occurs here
lock1.acquire()
Error:
Traceback (most recent call last):
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 143, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3301, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 447, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1363, in _checkout
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1301, in _checkout
result = pool._dialect._do_ping_w_event(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 728, in _do_ping_w_event
return self.do_ping(dbapi_connection)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/_psycopg_common.py", line 180, in do_ping
dbapi_connection.autocommit = True
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 182, in autocommit
self._set_autocommit(value)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 427, in _set_autocommit
self.set_autocommit(value)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 432, in set_autocommit
self.wait(self._set_autocommit_gen(value))
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 414, in wait
return waiting.wait(gen, self.pgconn.socket, interval=interval)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/waiting.py", line 333, in wait_poll
s = next(gen)
^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 188, in _set_autocommit_gen
yield from self._check_intrans_gen("autocommit")
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 260, in _check_intrans_gen
raise e.ProgrammingError(
psycopg.ProgrammingError: can't change 'autocommit' now: connection in transaction status INTRANS
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/moser/code/tendos/pals-exp/1.py", line 15, in <module>
lock1.acquire()
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/pals/core.py", line 136, in acquire
return self._acquire(blocking=blocking, acquire_timeout=acquire_timeout)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/pals/core.py", line 105, in _acquire
self.conn = self.engine.connect()
^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3277, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
Connection._handle_dbapi_exception_noconnection(
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 143, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3301, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 447, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1363, in _checkout
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1301, in _checkout
result = pool._dialect._do_ping_w_event(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 728, in _do_ping_w_event
return self.do_ping(dbapi_connection)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/_psycopg_common.py", line 180, in do_ping
dbapi_connection.autocommit = True
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 182, in autocommit
self._set_autocommit(value)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 427, in _set_autocommit
self.set_autocommit(value)
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 432, in set_autocommit
self.wait(self._set_autocommit_gen(value))
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 414, in wait
return waiting.wait(gen, self.pgconn.socket, interval=interval)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/waiting.py", line 333, in wait_poll
s = next(gen)
^^^^^^^^^
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 188, in _set_autocommit_gen
yield from self._check_intrans_gen("autocommit")
File "/home/moser/code/tendos/pals-exp/.venv/lib/python3.12/site-packages/psycopg/_connection_base.py", line 260, in _check_intrans_gen
raise e.ProgrammingError(
sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) can't change 'autocommit' now: connection in transaction status INTRANS
(Background on this error at: https://sqlalche.me/e/20/f405)
Relevant package versions:
pals 0.3.5
sqlalchemy 2.0.41
psycopg 3.2.9
I guess the problem is that the pool checkin event handler starts a transaction which is left open.
