In a Postgresql DB, mapped with SQLAlchemy, I have the following entities:

class ServiceProvider(Base):
__tablename__ = "service_provider"
__table_args__ = (
    ForeignKeyConstraint(["id"], ["anization.id"]),
    {"extend_existing": True},
)

id: Mapped[uuid.UUID] = mapped_column(sqltypes.UUID, primary_key=True)

anization: Mapped["Organization"] = relationship(
    lazy="selectin",
)

and

class Organization(Base):
    __tablename__ = "anization"

id: Mapped[uuid.UUID] = mapped_column(primary_key=True, index=True)

service_provider: Mapped[Optional[ServiceProvider]] = relationship(
    lazy="selectin",
    cascade="delete, delete-orphan",
    back_populates="anization",
    uselist=False,
)

When I do a delete on anization await session.delete(anization), that should cascade delete ServiceProvider, if he exists. Most of the time that works. However, I get random failures in my tests with the error

'IntegrityError(\'(sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class \\\'asyncpg.exceptions.ForeignKeyViolationError\\\'>: update or delete on table "anization" violates foreign key constraint "serviceprovider_id_fkey" on table "service_provider"\\nDETAIL:  Key (id)=(33693171-1297-4679-a73a-fff38bc9ce68) is still referenced from table "service_provider"

This happens at random intervals, it seems sometimes cascade delete from Organization to ServiceProvider doesn't work, and I'm struggling to understand why. I tried adding an explicit await session.delete(service_provider) before the Organization delete, but that causes an IntegrityError with a different table (service_provider_anization), and if I add a cascade delete from ServiceProvider to service_provider_anization, it seems to cause IntegrityErrors in other places). Why would cascade delete work sometimes and not others?

Edit: I want to add a note here, Organization has cascade delete to many other entities, not just ServiceProvider, and some of them have cascade delete to other entities as well. Maybe that has something to do with it, maybe when there are many cascade deletes there is a problem?

In a Postgresql DB, mapped with SQLAlchemy, I have the following entities:

class ServiceProvider(Base):
__tablename__ = "service_provider"
__table_args__ = (
    ForeignKeyConstraint(["id"], ["anization.id"]),
    {"extend_existing": True},
)

id: Mapped[uuid.UUID] = mapped_column(sqltypes.UUID, primary_key=True)

anization: Mapped["Organization"] = relationship(
    lazy="selectin",
)

and

class Organization(Base):
    __tablename__ = "anization"

id: Mapped[uuid.UUID] = mapped_column(primary_key=True, index=True)

service_provider: Mapped[Optional[ServiceProvider]] = relationship(
    lazy="selectin",
    cascade="delete, delete-orphan",
    back_populates="anization",
    uselist=False,
)

When I do a delete on anization await session.delete(anization), that should cascade delete ServiceProvider, if he exists. Most of the time that works. However, I get random failures in my tests with the error

'IntegrityError(\'(sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class \\\'asyncpg.exceptions.ForeignKeyViolationError\\\'>: update or delete on table "anization" violates foreign key constraint "serviceprovider_id_fkey" on table "service_provider"\\nDETAIL:  Key (id)=(33693171-1297-4679-a73a-fff38bc9ce68) is still referenced from table "service_provider"

This happens at random intervals, it seems sometimes cascade delete from Organization to ServiceProvider doesn't work, and I'm struggling to understand why. I tried adding an explicit await session.delete(service_provider) before the Organization delete, but that causes an IntegrityError with a different table (service_provider_anization), and if I add a cascade delete from ServiceProvider to service_provider_anization, it seems to cause IntegrityErrors in other places). Why would cascade delete work sometimes and not others?

Edit: I want to add a note here, Organization has cascade delete to many other entities, not just ServiceProvider, and some of them have cascade delete to other entities as well. Maybe that has something to do with it, maybe when there are many cascade deletes there is a problem?

• postgresql
• sqlalchemy

1 Answer 1

I think I found a solution. After some digging, I found that on some occasion anization.service_provider would be None (even though there was a service provider associated with the anization, and the relationship is defined as lazy selectin). And on those occasions cascade delete would not work and I would get the error. I solved it by adding await session.refresh(anization, attribute_names=["service_provider"])

That seems to have solved the problem.