asyncpg dialect improperly coerces the BigInteger type when passing it to the function polymorphic in its arguments #7814
-
Describe the bugSo, this is a really minor bug, which can be also related to asyncpg but I am not sure. The problem emerges when using asyncpg, it is not present when using psycopg2. It is easily reproducible, so I will let the code to speak for itself. Take following statement: When executed, it should fire the postgres
In our case, this is the first version which should be executed. And it does, but only if using The reason I think that this is a problem with SQLAlchemy, is that You can easily overcome this issue with explicit type coertion, which is a part of SQLAlchemy API. Use following statement: And everything runs flawlessly. To Reproducefrom sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy import select, func, BigInteger
from sqlalchemy.sql.expression import type_coerce
import asyncio
engine = create_async_engine(
"postgresql+asyncpg://postgres:1234567890@localhost:6543/postgres",
echo=True,
)
SessionLocal = sessionmaker(
class_=AsyncSession,
autocommit=False,
autoflush=False,
expire_on_commit=False,
future=True,
bind=engine,
)
async def main():
lock_id_64bit = 4944215145091319531
async with SessionLocal() as session:
# stmt = f"SELECT pg_try_advisory_lock({lock_id_64bit})"
stmt = select(
# This will work:
# func.pg_try_advisory_lock(type_coerce(lock_id_64bit, BigInteger()))
# But this not (only when using AsyncIO api, synchronous runs this without issues)
func.pg_try_advisory_lock(lock_id_64bit)
)
result = (await session.execute(stmt)).scalar_one()
print(result)
if __name__ == "__main__":
asyncio.run(main())ErrorFull traceback: VersionsSystem info
|
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 9 replies
-
|
Hi, sqlalchemy tries to guess what type to use based on the in argument. since python has only one kind of integer, that's what guessed. You can specify a type on the function, doing I think this is an expected behaviour, since |
Beta Was this translation helpful? Give feedback.
-
|
we can actually guess this one if we wanted, but im not sure what problems it would cause elsewhere. We now have a second level of "guessing" via the "_resolve_for_literal" method that you can see in commits like e215db0 . So we could add to Integer a resolve for literal that checks for int > 32 bits and go to BigInteger. |
Beta Was this translation helpful? Give feedback.
-
|
I still get the error with: rows = await session.execute(
sql.select([func.pg_try_advisory_xact_lock(lock_id_64bit, type_=BigInteger)])
)I am not using based on the example there I found that this form does work: func.pg_try_advisory_xact_lock(cast(lock_id_64bit, BigInteger))i.e. |
Beta Was this translation helpful? Give feedback.
-
|
I've tried and it indeed does not work. I've tested march versions and it too did not work, so I'm not sure if it ever worked on 1.4 Using |
Beta Was this translation helpful? Give feedback.
-
|
thanks for looking into it, yes I was on 1.4.41 |
Beta Was this translation helpful? Give feedback.
-
|
I ended up using this form instead: func.pg_try_advisory_xact_lock(literal(lock_id_64bit, BigInteger))as the |
Beta Was this translation helpful? Give feedback.
-
|
@CaselIT Yes, this would not bother me in any way, but... |
Beta Was this translation helpful? Give feedback.
-
|
did you use the psycopg2 driver with the sync Session? that would be why. psycopg2 does more introspection on the given datatypes and also renders them inline into a statement, whereas asyncpg uses prepared statements and doesn't assume very much about the incoming data. |
Beta Was this translation helpful? Give feedback.
-
|
as mike said the difference is psycopg2 vs asyncpg. since psycopg2 sends the sql string to the db with the parameters inline, it's postgresql that uses the correct signature pg8000 should lead to the same error when using the normal sync session |
Beta Was this translation helpful? Give feedback.
-
|
Now I understand :) Thank You guys for all replies. Have a nice day! |
Beta Was this translation helpful? Give feedback.
-
|
this should be working in 2.0 / github main. have we confirmed that? |
Beta Was this translation helpful? Give feedback.
-
|
confirmed this works in 2.0 without issue. so use the type coerce for now. |
Beta Was this translation helpful? Give feedback.
Hi,
sqlalchemy tries to guess what type to use based on the in argument. since python has only one kind of integer, that's what guessed. You can specify a type on the function, doing
select(func.pg_try_advisory_lock(lock_id_64bit, type_=BigInteger)I think this is an expected behaviour, since
pg_try_advisory_lockuses a generic function, so it has to guess the type from the arguments.