We're using PGDialect_psycopg2 and pgbouncer. In some cases, pgbouncer allows you to connect, but then connection may fail. The problem in that SQLAlchemy tries to initialize dialect only once, during this first connect, and if this initialization fails, SQLAlchemy doesn't trying to retry.
Our example. By default PGDialect_psycopg2._has_native_jsonb == False, and only during connection initialization this property becomes equal to True. When initialization fails on the first connect, SQLAlchemy then tries to decode already decoded by psycopg2 JSONB column values on subsequent connections.
Where strange things happen:
|
def initialize(self, connection): |
|
super(PGDialect_psycopg2, self).initialize(connection) |
|
self._has_native_hstore = ( |
|
self.use_native_hstore |
|
and self._hstore_oids(connection.connection) is not None |
|
) |
|
self._has_native_json = ( |
|
self.psycopg2_version >= self.FEATURE_VERSION_MAP["native_json"] |
|
) |
|
self._has_native_jsonb = ( |
|
self.psycopg2_version >= self.FEATURE_VERSION_MAP["native_jsonb"] |
|
) |
|
def first_connect(dbapi_connection, connection_record): |
|
c = base.Connection( |
|
engine, connection=dbapi_connection, _has_events=False |
|
) |
|
c._execution_options = util.immutabledict() |
|
dialect.initialize(c) |
|
dialect.do_rollback(c.connection) |
|
|
|
event.listen(pool, "first_connect", first_connect, once=True) |
Maybe it is safer to listen for every new connection and store somewhere the mark that dialect was already successfully initialized?
How to reproduce
Here is how to easily reproduce this error (tried master and 1.3.7 versions):
from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:postgres@db:5432/test')
try:
print(engine.execute('select 1;').fetchall())
except ZeroDivisionError as e:
print(e)
print(engine.execute('select 1;').fetchall())
print('_has_native_jsonb', engine.dialect._has_native_jsonb)Then just place 1/0 into PGDialect_psycopg2.initialize() method. Here is what this example prints:
division by zero
[(1,)]
_has_native_jsonb False
With our database we get this error on subsequent connections:
Traceback (most recent call last):
File "sandbox.py", line 54, in <module>
print(engine.execute(product.select().where(product.c.id == 709765476)).fetchall())
File "/Users/vm/ws/app/env/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 170, in __repr__
return repr(sql_util._repr_row(self))
File "/Users/vm/ws/app/env/lib/python3.7/site-packages/sqlalchemy/sql/util.py", line 458, in __repr__
", ".join(trunc(value) for value in self.row),
File "/Users/vm/ws/app/env/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py", line 2278, in process
return json_deserializer(value)
File "/usr/local/Cellar/python/3.7.2_2/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/__init__.py", line 341, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not dictWhen you remove 1/0 this error disappears.
We're using
PGDialect_psycopg2andpgbouncer. In some cases, pgbouncer allows you to connect, but then connection may fail. The problem in that SQLAlchemy tries to initialize dialect only once, during this first connect, and if this initialization fails, SQLAlchemy doesn't trying to retry.Our example. By default
PGDialect_psycopg2._has_native_jsonb == False, and only during connection initialization this property becomes equal toTrue. When initialization fails on the first connect, SQLAlchemy then tries to decode already decoded by psycopg2JSONBcolumn values on subsequent connections.Where strange things happen:
sqlalchemy/lib/sqlalchemy/dialects/postgresql/psycopg2.py
Lines 715 to 726 in d1948bc
sqlalchemy/lib/sqlalchemy/engine/create.py
Lines 524 to 532 in d1948bc
Maybe it is safer to
listenfor every new connection and store somewhere the mark that dialect was already successfully initialized?How to reproduce
Here is how to easily reproduce this error (tried master and 1.3.7 versions):
Then just place
1/0intoPGDialect_psycopg2.initialize()method. Here is what this example prints:With our database we get this error on subsequent connections:
When you remove
1/0this error disappears.