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

Most upvoted comments

I’ve seen a similar error with psycopg2 and PostgreSQL 9.6.