sqlalchemy: Reflection regression in postgresql
Apologies if this is a different issue, but I’m also seeing a regression with postgres after updating from 2.0.40 to 2.0.41. Something relating to how tables are reflected seems to have broken.
I’m using psycopg as the driver. Here’s the relevant error and traceback:
sqlalchemy.exc.InternalError: (psycopg.errors.InternalError_) failed to find conversion function from unknown to text
[SQL: SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, min(attr.indnkeyatts) AS indnkeyatts, bool_and(attr.indnullsnotdistinct) AS indnullsnotdistinct
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.description AS description, con.ord AS ord, con.indnkeyatts AS indnkeyatts, con.indnullsnotdistinct AS indnullsnotdistinct, pg_catalog.pg_attribute.attname AS attname
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, unnest(pg_catalog.pg_index.indkey) AS attnum, generate_subscripts(pg_catalog.pg_index.indkey, %(generate_subscripts_1)s::INTEGER) AS ord, NULL AS indnkeyatts, false AS indnullsnotdistinct, pg_catalog.pg_description.description AS description
FROM pg_catalog.pg_constraint JOIN pg_catalog.pg_index ON pg_catalog.pg_constraint.conindid = pg_catalog.pg_index.indexrelid LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid
WHERE pg_catalog.pg_constraint.contype = %(contype)s::VARCHAR AND pg_catalog.pg_constraint.conrelid IN (%(oids_1)s)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid
WHERE con.conrelid IN (%(oids_1)s)) AS attr GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname]
[parameters: {'generate_subscripts_1': 1, 'contype': 'p', 'oids_1': 1067720}]
(Background on this error at: https://sqlalche.me/e/20/2j85)
File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 59, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/dlt/extract/utils.py", line 260, in _parallel_gen
return next(gen) # type: ignore[call-overload]
^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/dlt/sources/sql_database/helpers.py", line 254, in table_rows
table = Table(
^^^^^^
File "<string>", line 2, in __new__
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
return fn(*args, **kwargs) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 429, in __new__
return cls._new(*args, **kw)
^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 483, in _new
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 479, in _new
table.__init__(name, metadata, *args, _no_init=False, **kw)
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 861, in __init__
self._autoload(
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 893, in _autoload
conn_insp.reflect_table(
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 1536, in reflect_table
_reflect_info = self._get_reflection_info(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 2022, in _get_reflection_info
pk_constraint=run(self.get_multi_pk_constraint),
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 2005, in run
res = meth(filter_names=_fn, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 1018, in get_multi_pk_constraint
return dict(
^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/base.py", line 4296, in <genexpr>
for table_name, cols, pk_name, comment, opts in result
^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/base.py", line 4211, in _reflect_constraint
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1415, in execute
return meth(
^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 523, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1982, in _exec_single_context
self._handle_dbapi_exception(
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2351, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
self.dialect.do_execute(
File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 943, in do_execute
cursor.execute(statement, parameters)
File "/app/.venv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
_Originally posted by @cbini in https://github.com/sqlalchemy/sqlalchemy/discussions/12592#discussioncomment-13172692_
Initial analysis this seems like a regression from https://github.com/sqlalchemy/sqlalchemy/pull/12485 that implemented https://github.com/sqlalchemy/sqlalchemy/issues/10665
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 3
- Comments: 19 (12 by maintainers)
Commits related to this issue
- Disallow SQLAlchemy 2.0.41 due to //github.com/sqlalchemy/sqlalchemy/issues/12600 . — committed to galaxybot/galaxy by nsoranzo 2 years ago
- Use pg_index's indnatts when indnkeyatts is not available Using NULL when this column is not available does not work with old PostgreSQL (tested on version 9.6, as reported in #12600). Instead, use ... — committed to dlax/sqlalchemy by dlax 2 years ago
- Use pg_index's indnatts when indnkeyatts is not available Using NULL when this column is not available does not work with old PostgreSQL (tested on version 9.6, as reported in #12600). Instead, use ... — committed to sqlalchemy/sqlalchemy by dlax 2 years ago
I’ve seen a similar error with psycopg2 and PostgreSQL 9.6.