Invalid OID Handling in SQLAlchemy with asyncpg and autoload_with Causes DataError
gtolarc opened this issue · 3 comments
gtolarc commented
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
- Create a PostgreSQL table with an OID that exceeds the int32 range. For example, an OID like 3195477613.
- 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)- 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 commented
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 commented
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 commented
Do you have the actual query post-interpolation? I think SQLAlchemy is probably injecting the wrong cast.