Untitled conversation
- User
classification_result = await classify_all_items( unclassified, group_definitions, current_user=current_user, batch_size=batch_size, ) classifications = classification_result["classified_groups"] # unique_items = [{v.id: v} for v in classifications.values()] classification_failures = classification_result["failed_items"] total_failed += len(classification_failures) logger.info("") for group_name, group_items in classifications.items(): # The LLM functions have aggregated item IDs into groups. # But now we need to match the group names to output feeds and put the items # into the DB. # We're matching based on name because the LLM isn't given IDs. # We can be confident that the name is unique in the catalogue. logger.debug( f"Trying to find an output feed called '{group_name}' to match it to the " "LLM response", extra={"group_name": group_name}, ) output_feed = next( (feed for feed in output_feeds if feed.title == group_name), None ) if not output_feed: logger.error( f"LLM gave name {group_name}, but it's not an output feed.", extra={"group_name": group_name}, ) # All items in this group are consequently failed total_failed += len(group_items) continue logger.debug(f"Found an output feed called '{group_name}', now matching items") for llm_item in group_items: db_item = next( (item for item in unclassified if item.id == llm_item.id), None, ) if db_item: await session.refresh(db_item) existing_classifications_result = await session.exec( select(ItemFeedLink).where( ItemFeedLink.item_id == db_item.id, ItemFeedLink.feed_id.in_([feed.id for feed in output_feeds]), ) ) existing_classifications = existing_classifications_result.all() if existing_classifications: logger.warning( "Surprised to find at least one existing classification", extra={ "item_id": db_item.id, "feed_id": existing_classifications.feed_id, }, ) await session.delete(existing_classifications) updated_classification = ItemFeedLink( item_id=db_item.id, feed_id=output_feed.id ) session.add(updated_classification) classified_group_counts[group_name] += 1 total_classified += 1 else: logger.error( f"Couldn't find item with id {llm_item.id}", extra={"item_id": llm_item.id}, ) total_failed += 1 await session.commit()Does this SQLAlchemy session management work the way I think it does? I am specifically concerned about IntegrityErrors
- Assistant
Your code shows a SQLAlchemy asynchronous session flow using
session.add(...),session.delete(...), and finallyawait session.commit(). You’re asking whether this will work as you think, with special concern towardIntegrityErrors.Let's break down the key parts of your SQLAlchemy session usage and address your concerns:
✅ What’s correct in your usage:
-
Session usage is async-aware: You're using
await session.exec,await session.refresh,await session.commit()— all correct patterns in SQLAlchemy 2.0+ async APIs. -
Modifications accumulate until commit: You’re adding (
session.add(...)) newItemFeedLinkentries and deleting (session.delete(...)) existing ones within the same session/transaction. Changes do not hit the DB until youawait session.commit()— expected behavior. -
You're tracking total failed/classified counters manually — good for logging or recovery.**
-
You refresh
db_itemfrom DB before making changes, which ensures you are working with fresh data.
⚠️ Where issues can arise — especially
IntegrityErrorand protections against it:
1.
IntegrityErrorrisk not being caughtYou do not have any
try/exceptblocks aroundawait session.commit(). This is dangerous if you're concerned about:- Uniqueness constraint violations (e.g., there's already a
ItemFeedLinkwith sameitem_idandfeed_id) - Foreign key violations (if items don’t exist as referenced)
Fix: Wrap
commit()in a try block:from sqlalchemy.exc import IntegrityError try: await session.commit() except IntegrityError as e: await session.rollback() logger.exception("Integrity error during classification commit", extra={"error": str(e)}) # Depending on logic, you could re-raise or collect failed casesThis gives you visibility into the failure and ensures your session doesn’t get stuck in a broken state (which happens after failed commits).
2. Deleting many items — improper handling?
You do this:
await session.delete(existing_classifications)But
existing_classificationsis a list of objects, andsession.delete()expects a single ORM instance.➡️ Potential fix:
for existing in existing_classifications: await session.delete(existing)Otherwise, this line may error silently or throw a TypeError or ORM warning.
3. Racing conditions & uniqueness/clashes
It's possible that if this runs in parallel (multiple calls at once/classifying same items concurrently), you may run into:
- Two workers deleting and inserting
ItemFeedLinkfor the same item - One inserting before the other deletes — causing duplicate rows and thus violating a unique composite constraint (like
(item_id, feed_id)).
➡️ Fix/prevention tips:
- Use database-level constraints properly (
UNIQUE(item_id, feed_id)) - Inserting with
ON CONFLICT DO UPDATEpattern is safer — requires handling at SQL level or using upserts (FastAPI + SQLAlchemy doesn’t do this out of the box — would need raw SQL or SQLModel Mixins)
4. No explicit session lifecycle management
You haven’t shown the outer part of the session handling logic (
async with session:or sessionmaker) — but assuming that’s handled, this code should work. However, if there's any sharing or long-lived session, unresolved errors (IntegrityError during commit) leave session in an inconsistent state.➡️ Always
rollback()oncommit()failure.
✅ Summary of Recommendations
Concern Status Recommendation Async-safe session usage ✅ Yes Keep using awaitwith session opsIntegrityErrorrisks⚠️ Not handled Wrap commit()intry/except; rollback on errorDeleting many objects ❌ Incorrect usage Loop through existing_classificationsand delete them individuallyUniqueness constraints ⚠️ Possible race condition Add UNIQUE(item_id, feed_id)at DB level, use transaction isolation, or upsert logicSession lifecycle Not shown Ensure you're using async with sessionor properly closing/cleaning up sessions
Example Enhanced Commit Block
try: await session.commit() except IntegrityError as e: await session.rollback() logger.exception("Integrity error during classification commit", extra={"error": str(e)}) # Optionally: handle specific item(s) that triggered the error raise
If you want help writing a fault-tolerant version of this code (e.g. commit per item or per group), let me know!
-
- User
This currently sits within a loop:
@app.post("/catalogue/classified") async def classify_all_catalogues( paid_tier_only: bool = True, current_user: user = Depends(get_current_user), session: AsyncSession = Depends(get_session), ): if not is_admin(current_user): raise HTTPException( status_code=status.HTTP_403_FORBIDDEN, detail="You can only classify your own catalogues", ) # TODO: Randomise the order if paid_tier_only: # Get paid tier users with active subscriptions paid_tier_users_result = await session.exec( select(user) .join(Subscription) .where( Subscription.status.in_( [ SubscriptionStatus.ACTIVE, SubscriptionStatus.TRIALING, SubscriptionStatus.PAST_DUE, ] ), Subscription.end_date > datetime.now(UTC), ) .distinct() ) paid_tier_users = paid_tier_users_result.all() # Get catalogues from paid tier users catalogues_result = await session.exec( select(Catalogue).where( Catalogue.user_id.in_([user.id for user in paid_tier_users]) ) ) catalogues = catalogues_result.all() logger.debug( f"Classifying {len(catalogues)} catalogues for paid tier users", extra={"catalogues": [catalogue.id for catalogue in catalogues]}, ) else: catalogues_result = await session.exec(select(Catalogue)) catalogues = catalogues_result.all() logger.debug( f"Classifying {len(catalogues)} catalogues", extra={"catalogues": [catalogue.id for catalogue in catalogues]}, ) for catalogue in catalogues: try: await classify_catalogue( catalogue.id, current_user=current_user, session=session ) except Exception as err: logger.critical( f"Unexpected error classifying catalogue {catalogue.id}: {err}", exc_info=True, ) continue return {"message": f"Classified {len(catalogues)} catalogues"}What are the implications of doing the session.commit() / session.rollback() now?
- Assistant
Great follow-up question — the fact that you're passing the same
AsyncSessioninto a loop across multiple operations, and doingsession.commit()and possibly needingsession.rollback()inside that loop, raises some important design and correctness considerations with respect to the SQLAlchemy session lifecycle, transactional integrity, and error isolation.Let’s break this down.
🚨 Key Insight
You're using a single shared SQLAlchemy
AsyncSession:session: AsyncSession = Depends(get_session)And then reusing it across multiple calls to:
await classify_catalogue(catalogue.id, ...)Inside
classify_catalogue, you make multiple DB changes and finally do:await session.commit()
⚠️ Implications of Sharing a Session With
commit()/rollback()in a LoopLet’s walk through what this design means and where risks come in.
1. ✅ Shared Session Allows You to Avoid Re-initializing the Connection Pool
This is good in theory: you're not opening and closing the DB session per catalogue, so performance-wise it looks more efficient.
However...
2. ⚠️ A Failed Commit Corrupts the Session for All Subsequent Catalogue Iterations
If any single
await session.commit()fails, you must callawait session.rollback(), or that session remains in a failed/closed state.If you don’t, then the next call to
classify_catalogue()using the same shared session will raise errors even if it does nothing wrong, because the session is corrupt.
3. ❌ Your Current Error Handling Doesn't Handle Rollbacks
Your current loop does this:
for catalogue in catalogues: try: await classify_catalogue(catalogue.id, current_user=current_user, session=session) except Exception as err: logger.critical(...) continueBut if there's an
IntegrityErrorwithinclassify_catalogue()(e.g., duplicate key, bad FK), then that function commits and fails—but your outer loop doesn't trigger a rollback, and the session is now broken for future use.➡️ After the first exception, all other classifications will fail, too.
✅ Best Practices for This Pattern
Now let’s talk solutions. You have a few options to make this robust:
✅ Option A: Handle Commit/Rollback Inside
classify_catalogue()— Fully Isolated TransactionsIf you're going to keep a shared session, then every time you call
session.commit()inside an inner function, you must roll back if something goes wrong.So structure
classify_catalogue()like:try: await session.commit() except IntegrityError as e: await session.rollback() logger.exception("Integrity error during commit") raise # Or handle gracefullyThis way, rollback is handled at the local transaction boundary, and outer loop can continue safely.
✅ Option B: Pass an Independent Session Per Catalogue — Transaction per call
A cleaner solution in many cases is to make the classification of each catalogue happen under its own session, e.g.:
from app.database import get_session_local # Or however you instantiate sessions for catalogue in catalogues: async with get_session_local() as session: try: await classify_catalogue(catalogue.id, current_user=current_user, session=session) except Exception as err: logger.critical( f"Unexpected error classifying catalogue {catalogue.id}: {err}", exc_info=True, ) continueThis way, each catalogue’s classification is totally isolated:
- ❌ A failed operation doesn't poison the entire loop
- ✅ The transaction (commit/rollback) scope matches the catalogue
- ✅ Automatic cleanup happens due to
async with session
✅ Option C: Session-per-catalogue AND
classify_catalogueownscommit()logicCombine Option A and B. That is:
classify_catalogue(...)decides exactly when to commit/rollbackAsyncSessionis created fresh for each catalogue- Outer loop doesn't worry about rollback
🔄 Makes unit testing easier, too.
✳️ Additional Considerations
🧪 1. Test What Happens After Exceptions
Ensure that after one failed
commit(), nothing else in the loop breaks unless expected. Use dummy data to force a known IntegrityError and make sure other catalogues still classify.
🔐 2. Match Your DB Model to Constraints You Expect
If you're getting
IntegrityErrors around duplicateItemFeedLink, ensure your DB schema has the appropriate unique constraints, so you're catching real bugs consistently.
✅ Final Recommendation (Best Practice Summary)
TL;DR — Since you’re dealing with potentially dangerous commits and multiple catalogues, do this:
✅ Recommended Loop:
for catalogue in catalogues: async with get_session_local() as session: try: await classify_catalogue(catalogue.id, current_user=current_user, session=session) await session.commit() # Move commit here if classify_catalogue does not commit except IntegrityError as e: await session.rollback() logger.error( f"IntegrityError classifying catalogue {catalogue.id}: {e}", exc_info=True ) except Exception as err: await session.rollback() logger.critical( f"Unexpected error classifying catalogue {catalogue.id}: {err}", exc_info=True, )Where:
get_session_local()= your session factory (like whatDepends(get_session)uses)classify_catalogue()should ideally not commit, and let the caller handle it.
Let me know if you want help restructuring
classify_catalogue()to tolerate this commit/rollback pattern or control the DB boundaries better!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.