Database Transaction Handling

This guide explains how database transactions are handled in the API, particularly focusing on error handling and transaction management.

Transaction Error Handling

The API uses SQLAlchemy for database operations, and transactions are managed through the SQLAlchemy session. When a transaction fails, it enters an "aborted" state, and PostgreSQL will reject any further commands until the transaction is either committed or rolled back.

Common Transaction Errors

One common error you might encounter is:

InternalError: (psycopg.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block

This error occurs when:

1. A previous operation in the transaction failed
2. The transaction was not rolled back
3. Subsequent operations are attempted within the same transaction

How We Handle Transaction Errors

To address these issues, we've implemented several layers of transaction error handling:

1. Session-level error handling in app/api/deps.py:
2. The get_db dependency catches exceptions, rolls back the transaction, and re-raises the exception

3. This ensures that failed transactions don't propagate to subsequent requests

4. CRUD-level error handling in app/crud/base.py:

5. Base methods like get and get_multi include try-except blocks
6. On error, they roll back the transaction and retry the operation

7. This provides a second layer of protection for common database operations

8. Specialized error handling in app/crud/production.py:

9. Methods that load relationships (like get_full, get_with_trackers, etc.) use eager loading with joinedload
10. They include try-except blocks to handle errors and retry with simpler queries if needed

11. This prevents lazy loading errors when accessing relationships

12. Coordinates conversion for PostGIS geography:

13. Storage and delivery locations use PostGIS geography for location data, but the API expects a coordinates object
14. We've added a _process_locations method that converts PostGIS geography to coordinates
15. This ensures that all location types have the correct format for serialization

16. If the conversion fails, we provide default coordinates to prevent validation errors

17. Geography vs. Geometry type mismatch:

18. The database schema uses Geography type for location columns, but the models were incorrectly using Geometry
19. This mismatch caused errors with PostGIS functions like ST_AsEWKB which have different signatures for geography and geometry types
20. We've updated all models to use Geography to match the database schema

21. This ensures that the correct PostGIS functions are used for the data type

22. Location History Coordinates Processing:

23. The location history endpoint was returning objects without the required coordinates field
24. We've updated the endpoint to process each location history object and add the coordinates
25. The coordinates are extracted from the PostGIS geography using ST_X and ST_Y functions
26. This ensures that the API response matches the expected schema

Best Practices

When working with database operations:

1. Use transactions appropriately:
2. Keep transactions as short as possible
3. Commit or rollback transactions promptly

4. Don't leave transactions open across multiple operations

5. Handle errors at the appropriate level:

6. Use try-except blocks around database operations
7. Roll back transactions when errors occur

8. Log errors for debugging

9. Consider eager loading for relationships:

10. Use joinedload to load relationships eagerly when you know you'll need them

11. This prevents lazy loading errors when accessing relationships later

12. Monitor for transaction issues:

13. Watch for error logs indicating transaction failures
14. Check for performance issues that might be related to transaction management

Example: Handling Transaction Errors

def get_item_with_relationships(db: Session, item_id: int):
    try:
        # Eager loading of relationships
        from sqlalchemy.orm import joinedload

        return (
            db.query(Item)
            .options(
                joinedload(Item.related_items),
                joinedload(Item.tags)
            )
            .filter(Item.id == item_id)
            .first()
        )
    except Exception as e:
        # Roll back the transaction and try again with a simpler query
        db.rollback()
        print(f"Error in get_item_with_relationships: {e}")
        return db.query(Item).filter(Item.id == item_id).first()

This pattern ensures that even if the eager loading query fails, we can still retrieve the basic item data.