Skip to content

Committing two or more ORM objects with UUID columns causes DBAPIError #9808

New issue
New issue
Closed
Closed
Committing two or more ORM objects with UUID columns causes DBAPIError#9808
Labels
bugSomething isn't workingnear-term releaseaddition to the milestone which indicates this should be in a near-term releaseormpostgresql
Milestone
 
2.0.x
@miguelgrinberg

Description

@miguelgrinberg
miguelgrinberg
opened on May 19, 2023 · edited by miguelgrinberg
Contributor

Describe the bug

This appears to be another regression caused by #9618. Similar (yet not identical) to #9739.

The issue occurs with the asyncpg driver, for models that have a column of type UUID. Adding one entry at a time works fine, here is an example SQL statement:

INSERT INTO b (a_id) VALUES ($1) RETURNING b.id

When adding two or more objects, this is the SQL that is generated:

INSERT INTO b (a_id) SELECT p0::UUID FROM (VALUES ($1, 0), ($2, 1)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING b.id, b.id AS id__1

This second statement generates the following error:

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: UUID('6fa30cae-d519-45f6-8f2e-69dde86a94... (expected str, got UUID)

For comparison, this is the (working) SQL generated when adding two rows using the psycopg2 driver:

INSERT INTO b (a_id) SELECT p0::UUID FROM (VALUES (%(a_id__0)s, 0), (%(a_id__1)s, 1)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING b.id, b.id AS id__1

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.13

DBAPI (i.e. the database driver)

asyncpg

Database Vendor and Major Version

PostgreSQL 15.1

Python Version

3.11

Operating system

OSX

To Reproduce

import os
from dotenv import load_dotenv
import sqlalchemy as sa
import sqlalchemy.orm as so
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
import asyncio
from uuid import uuid4, UUID


class Model(so.DeclarativeBase):
    pass

load_dotenv()

engine = create_async_engine(os.environ['DATABASE_URL'], echo=True)
Session = async_sessionmaker(engine, expire_on_commit=False)


class A(Model):
    __tablename__ = 'a'
    id: so.Mapped[UUID] = so.mapped_column(default=uuid4, primary_key=True)


class B(Model):
    __tablename__ = 'b'
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    a_id: so.Mapped[UUID] = so.mapped_column(sa.ForeignKey('a.id'))
    a = so.relationship(A)


async def main():
    async with engine.begin() as conn:
        await conn.run_sync(Model.metadata.drop_all)
        await conn.run_sync(Model.metadata.create_all)
    async with Session() as session:
        a = A(id=uuid4())
        #bs = [B(a=a)]
        bs = [B(a=a), B(a=a)]
        session.add_all(bs)
        await session.commit()
    async with engine.begin() as conn:
        await conn.run_sync(Model.metadata.drop_all)


asyncio.run(main())

Error

2023-05-19 19:13:47,305 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-05-19 19:13:47,305 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-19 19:13:47,311 INFO sqlalchemy.engine.Engine select current_schema()
2023-05-19 19:13:47,311 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-19 19:13:47,315 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-05-19 19:13:47,316 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-19 19:13:47,319 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-19 19:13:47,323 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = $1::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[$2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::VARCHAR, $6::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $7::VARCHAR
2023-05-19 19:13:47,323 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ('a', 'r', 'p', 'f', 'v', 'm', 'pg_catalog')
2023-05-19 19:13:47,329 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = $1::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[$2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::VARCHAR, $6::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $7::VARCHAR
2023-05-19 19:13:47,329 INFO sqlalchemy.engine.Engine [cached since 0.006091s ago] ('b', 'r', 'p', 'f', 'v', 'm', 'pg_catalog')
2023-05-19 19:13:47,330 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = $1::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[$2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::VARCHAR, $6::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $7::VARCHAR
2023-05-19 19:13:47,330 INFO sqlalchemy.engine.Engine [cached since 0.007731s ago] ('a', 'r', 'p', 'f', 'v', 'm', 'pg_catalog')
2023-05-19 19:13:47,332 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = $1::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[$2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::VARCHAR, $6::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $7::VARCHAR
2023-05-19 19:13:47,332 INFO sqlalchemy.engine.Engine [cached since 0.00911s ago] ('b', 'r', 'p', 'f', 'v', 'm', 'pg_catalog')
2023-05-19 19:13:47,333 INFO sqlalchemy.engine.Engine
CREATE TABLE a (
        id UUID NOT NULL,
        PRIMARY KEY (id)
)


2023-05-19 19:13:47,333 INFO sqlalchemy.engine.Engine [no key 0.00009s] ()
2023-05-19 19:13:47,340 INFO sqlalchemy.engine.Engine
CREATE TABLE b (
        id SERIAL NOT NULL,
        a_id UUID NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(a_id) REFERENCES a (id)
)


2023-05-19 19:13:47,340 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
2023-05-19 19:13:47,346 INFO sqlalchemy.engine.Engine COMMIT
2023-05-19 19:13:47,351 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-19 19:13:47,352 INFO sqlalchemy.engine.Engine INSERT INTO a (id) VALUES ($1)
2023-05-19 19:13:47,352 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (UUID('6fa30cae-d519-45f6-8f2e-69dde86a94c2'),)
2023-05-19 19:13:47,358 INFO sqlalchemy.engine.Engine INSERT INTO b (a_id) SELECT p0::UUID FROM (VALUES ($1, 0), ($2, 1)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING b.id, b.id AS id__1
2023-05-19 19:13:47,358 INFO sqlalchemy.engine.Engine [generated in 0.00007s (insertmanyvalues) 1/1 (ordered)] (UUID('6fa30cae-d519-45f6-8f2e-69dde86a94c2'), UUID('6fa30cae-d519-45f6-8f2e-69dde86a94c2'))
2023-05-19 19:13:47,360 INFO sqlalchemy.engine.Engine ROLLBACK
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/text.pyx", line 29, in asyncpg.pgproto.pgproto.text_encode
  File "asyncpg/pgproto/./codecs/text.pyx", line 12, in asyncpg.pgproto.pgproto.as_pg_string_and_size
TypeError: expected str, got UUID

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

Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 531, in _prepare_and_execute
    self._rows = await prepared_stmt.fetch(*parameters)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 176, in fetch
    data = await self.__bind_execute(args, 0, timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 241, in __bind_execute
    data, status, _ = await self.__do_execute(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/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: UUID('6fa30cae-d519-45f6-8f2e-69dde86a94... (expected str, got UUID)

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

Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
    dialect.do_execute(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 564, in execute
    self._adapt_connection.await_(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 126, in await_only
    return current.driver.switch(awaitable)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 187, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 543, in _prepare_and_execute
    self._handle_exception(error)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 493, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 782, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.Error: <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: UUID('6fa30cae-d519-45f6-8f2e-69dde86a94... (expected str, got UUID)

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

Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/retrofun/practice/postgresql/uuid-bug/models.py", line 45, in <module>
    asyncio.run(main())
  File "/Users/mgrinberg/.pyenv/versions/3.11.0/lib/python3.11/asyncio/runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/.pyenv/versions/3.11.0/lib/python3.11/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/.pyenv/versions/3.11.0/lib/python3.11/asyncio/base_events.py", line 650, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/practice/postgresql/uuid-bug/models.py", line 40, in main
    await session.commit()
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/ext/asyncio/session.py", line 932, in commit
    await greenlet_spawn(self.sync_session.commit)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 194, in greenlet_spawn
    result = context.switch(value)
             ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1906, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1221, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1196, in _prepare_impl
    self.session.flush()
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4154, in flush
    self._flush(objects)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4290, in _flush
    with util.safe_reraise():
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4251, in _flush
    flush_context.execute()
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 467, in execute
    rec.execute(self)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 644, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1133, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
    return meth(
           ^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1836, in _execute_context
    return self._exec_insertmany_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
    dialect.do_execute(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 564, in execute
    self._adapt_connection.await_(
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 126, in await_only
    return current.driver.switch(awaitable)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 187, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 543, in _prepare_and_execute
    self._handle_exception(error)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 493, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/Users/mgrinberg/Documents/dev/python/retrofun/venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 782, 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: UUID('6fa30cae-d519-45f6-8f2e-69dde86a94... (expected str, got UUID)
[SQL: INSERT INTO b (a_id) SELECT p0::UUID FROM (VALUES ($1, 0), ($2, 1)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING b.id, b.id AS id__1]
[parameters: (UUID('6fa30cae-d519-45f6-8f2e-69dde86a94c2'), UUID('6fa30cae-d519-45f6-8f2e-69dde86a94c2'))]
(Background on this error at: https://sqlalche.me/e/20/dbapi)

Additional context

No response

Activity

miguelgrinberg
added
requires triageNew issue that requires categorization
on May 19, 2023
miguelgrinberg
mentioned this on May 19, 2023
zzzeek
added
bugSomething isn't working
postgresql
orm
near-term releaseaddition to the milestone which indicates this should be in a near-term release
and removed
requires triageNew issue that requires categorization
on May 19, 2023
zzzeek
added this to the 2.0.x milestone on May 19, 2023
zzzeek

zzzeek commented on May 19, 2023

@zzzeek
zzzeek
on May 19, 2023
Member

this will work with:

Uuid().with_variant(UUID(), "postgresql")

for now

sqla-tester

sqla-tester commented on May 19, 2023

@sqla-tester
sqla-tester
on May 19, 2023
Collaborator

Mike Bayer has proposed a fix for this issue in the main branch:

establish Uuid as emulated, pg.UUID as nativeforemulated https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4623

sqlalchemy-bot
closed this as completedin ee6ab85on May 19, 2023
soldag
mentioned this on Oct 24, 2023
lelit
added a commit that references this issue on Feb 9, 2024

establish Uuid as emulated, pg.UUID as nativeforemulated

ee6ab85
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingnear-term releaseaddition to the milestone which indicates this should be in a near-term releaseormpostgresql

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@miguelgrinberg@sqla-tester

        Issue actions
          Committing two or more ORM objects with UUID columns causes DBAPIError · Issue #9808 · sqlalchemy/sqlalchemy