Postgres insert many causes InvalidRequestError: Can't match sentinel values in result set to parameter sets

[shabani1]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

import asyncio
import uuid
from datetime import datetime
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlmodel import Field, SQLModel, Column, DateTime, func
from sqlmodel.ext.asyncio.session import AsyncSession

engine = create_async_engine("postgresql+asyncpg://postgres:postgres@localhost:5432/test_db", echo=True)
AsyncSessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


class Item(SQLModel, table=True):
    __tablename__ = 'items'
    name: str
    item_id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    created_at: datetime = Field(
        sa_column=Column(DateTime(timezone=True), nullable=False, server_default=func.now()),
    )
    updated_at: datetime = Field(
        sa_column=Column(DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now()),
    )
    collection_id: str = Field(default='default')


async def create_tables():
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)

async def drop_tables():
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.drop_all)

async def insert_item():
    async with AsyncSessionLocal() as session:
        async with session.begin():
            item = Item(name='This is an item')
            session.add(item)
            await session.commit()

async def insert_two_items():
    async with AsyncSessionLocal() as session:
        async with session.begin():
            item1 = Item(name='This is item 1')
            item2 = Item(name='This is item 2')
            session.add(item1)
            session.add(item2)
            await session.commit()

async def async_main():
    await drop_tables()
    await create_tables()
    await insert_item()  # this works
    await insert_two_items()  # this fails
    await drop_tables()

if __name__ == "__main__":
    asyncio.run(async_main())

Description

I have a UUID field as my primary key, and am inserting items as seen in the code above. Whenever I add two items to my session and commit, it leads to the following error:

InvalidRequestError: Can't match sentinel values in result set to parameter sets; key '34bdaa33-90fe-4058-a278-3561fe04ad70' was not found. There may be a mismatch between the datatype passed to the DBAPI driver vs. that which it returns in a result row. Ensure the given Python value matches the expected result type *exactly*, taking care to not rely upon implicit conversions which may occur such as when using strings in place of UUID or integer values, etc.

• The columns created_at and updated_at seem to cause the error -- without these columns it works as expected.
• I've created a SQLAlchemy issue here, which reproduces the error without using SQLModel, but it turns out this is expected behavior.
• The GUID class could be updated as described here to include _sentinel_value_resolver, or can be swapped out for the Uuid type as recommended here and here.

Operating System

macOS

Operating System Details

sqlalchemy==2.0.28

SQLModel Version

0.0.16

Python Version

3.11

Additional Context

No response