Invalid OID Handling in SQLAlchemy with asyncpg and autoload_with Causes DataError

[gtolarc]

Describe the bug

I encountered an issue when using SQLAlchemy with asyncpg and the autoload_with option while trying to load table metadata. The problem occurs because PostgreSQL's oid type is treated as a signed 32-bit integer (int32) by asyncpg, which leads to a DataError when the OID value exceeds the maximum range of int32 (2,147,483,647).

Steps to Reproduce

1. Create a PostgreSQL table with an OID that exceeds the int32 range. For example, an OID like 3195477613.
2. Use SQLAlchemy to define the table with autoload_with to load metadata:

from sqlalchemy import Table, MetaData

metadata = MetaData()
table = Table("master_product_view", metadata, autoload_with=conn)

3. Execute the code.

Observed Behavior

The following exception is raised during metadata loading:

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: 3195477613 (value out of int32 range)

Expected Behavior

The autoload_with option should correctly handle OID values, even if they exceed the int32 range.

asyncpg/SQLAlchemy Version in Use

asyncpg 0.30.0 sqlalchemy 1.4.54

Database Vendor and Major Version

PostgreSQL 14

Python Version

3.13

Operating system

Linux

[elprans]

Hmm. AFAICS OIDs are coded correctly as unisgned int32's, can you post the entire traceback for the error you're getting please?

[rafmagns-skepa-dreag]

hi @elprans I have the same issue (but on asyncpg 0.27, python 3.8). I have attached my traceback

Traceback (most recent call last):
  File "asyncpg/protocol/prepared_stmt.pyx", line 168, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
  File "asyncpg/protocol/codecs/base.pyx", line 206, in asyncpg.protocol.protocol.Codec.encode
  File "asyncpg/protocol/codecs/base.pyx", line 111, in asyncpg.protocol.protocol.Codec.encode_scalar
  File "asyncpg/pgproto/./codecs/int.pyx", line 60, in asyncpg.pgproto.pgproto.int4_encode
OverflowError: value out of int32 range

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 442, in _prepare_and_execute
    self._rows = await prepared_stmt.fetch(*parameters)
  File "/home/project/.venv/lib/python3.8/site-packages/asyncpg/prepared_stmt.py", line 176, in fetch
    data = await self.__bind_execute(args, 0, timeout)
  File "/home/project/.venv/lib/python3.8/site-packages/asyncpg/prepared_stmt.py", line 241, in __bind_execute
    data, status, _ = await self.__do_execute(
  File "/home/project/.venv/lib/python3.8/site-packages/asyncpg/prepared_stmt.py", line 230, in __do_execute
    return await executor(protocol)
  File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute
  File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: 2484853517 (value out of int32 range)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 479, in execute
    self._adapt_connection.await_(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 454, in _prepare_and_execute
    self._handle_exception(error)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 389, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 682, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.Error: <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: 2484853517 (value out of int32 range)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "tests/common/test_models.py", line 647, in <module>
    asyncio.run(run_test_with_env(sys.argv[1]))
  File "/home/project/.cache/bazel/_bazel_project/e221516f56438dc18123021bac12eb81/external/python3_8_x86_64-unknown-linux-gnu/lib/python3.8/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/home/project/.cache/bazel/_bazel_project/e221516f56438dc18123021bac12eb81/external/python3_8_x86_64-unknown-linux-gnu/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "tests/common/test_models.py", line 627, in run_test_with_env
    await test_sqlalchemy_models_match_db(session)
  File "tests/common/test_models.py", line 278, in test_sqlalchemy_models_match_db
    await conn.run_sync(partial(md.reflect, views=True))
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/ext/asyncio/engine.py", line 548, in run_sync
    return await greenlet_spawn(fn, conn, *arg, **kw)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 126, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4901, in reflect
    Table(name, self, **reflect_opts)
  File "<string>", line 2, in __new__
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
    return fn(*args, **kwargs)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 619, in __new__
    metadata._remove_table(name, schema)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 614, in __new__
    table._init(name, metadata, *args, **kw)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 689, in _init
    self._autoload(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload
    conn_insp.reflect_table(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 774, in reflect_table
    for col_d in self.get_columns(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 497, in get_columns
    col_defs = self.dialect.get_columns(
  File "<string>", line 2, in get_columns
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
    ret = fn(self, con, *args, **kw)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3918, in get_columns
    c = connection.execute(s, dict(table_oid=table_oid))
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 479, in execute
    self._adapt_connection.await_(
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 454, in _prepare_and_execute
    self._handle_exception(error)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 389, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/home/project/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 682, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: 2484853517 (value out of int32 range)
[SQL:
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (
                SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
                AND a.atthasdef
              ) AS DEFAULT,
              a.attnotnull,
              a.attrelid as table_oid,
              pgd.description as comment,
              a.attgenerated as generated,
                              (SELECT json_build_object(
                    'always', a.attidentity = 'a',
                    'start', s.seqstart,
                    'increment', s.seqincrement,
                    'minvalue', s.seqmin,
                    'maxvalue', s.seqmax,
                    'cache', s.seqcache,
                    'cycle', s.seqcycle)
                FROM pg_catalog.pg_sequence s
                JOIN pg_catalog.pg_class c on s.seqrelid = c."oid"
                WHERE c.relkind = 'S'
                AND a.attidentity != ''
                AND s.seqrelid = pg_catalog.pg_get_serial_sequence(
                    a.attrelid::regclass::text, a.attname
                )::regclass::oid
                ) as identity_options
            FROM pg_catalog.pg_attribute a
            LEFT JOIN pg_catalog.pg_description pgd ON (
                pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
            WHERE a.attrelid = %s
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum
        ]
[parameters: (2484853517,)]
(Background on this error at: https://sqlalche.me/e/14/dbapi)

[elprans]

Do you have the actual query post-interpolation? I think SQLAlchemy is probably injecting the wrong cast.