[Python] Sqlalchemy delete on table "tasks" violates foreign key constraint for cascade mode

I have the following postgres table

dbname=# \d+ tasks
Table "public.tasks"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | character varying | | not null | | extended | |
external_id | character varying | | not null | | extended | |
rq_id | character varying | | not null | | extended | |
priority | integer | | not null | | plain | |
status | taskstatus | | not null | | plain | |
creation_time | timestamp without time zone | | not null | | plain | |
other fields ...
Indexes:
"tasks_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "solutions" CONSTRAINT "solutions_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id)
TABLE "tasks_files" CONSTRAINT "tasks_files_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id)
Access method: heap


The table created with help of sqlalchemy class

class Task(Base):
__tablename__ = 'tasks'

id = Column(String, unique=True, nullable=False, primary_key=True)
external_id = Column(String, nullable=False)
rq_id = Column(String, nullable=False)
priority = Column(Integer, nullable=False)
status = Column(Enum(TaskStatus), nullable=False)
creation_time = Column(DateTime, nullable=False)
...
files: Mapped[List["TaskFile"]] = relationship(cascade="all, delete")
solution: Mapped[List["Solution"]] = relationship(cascade="all, delete")


I have a python function, the function running SQL command and I have an error:


dbname=# DELETE FROM tasks WHERE tasks.status = 'SOLVED' AND tasks.creation_time <= '2025-08-07T08:15:40.867870'::timestamp OR tasks.status = 'ERROR' AND tasks.creation_time <= '2025-08-07T08:15:40.867877'::timestamp RETURNING tasks.id;
ERROR: update or delete on table "tasks" violates foreign key constraint "solutions_task_id_fkey" on table "solutions"
DETAIL: Key (id)=(7bff9459-2ef0-4650-8f3e-6774203e10fb) is still referenced from table "solutions".


def f(db, solved_task_window_days: int, error_task_window_days: int) -> None:
solved_days_ago = datetime.utcnow() - timedelta(days=solved_task_window_days)
failed_days_ago = datetime.utcnow() - timedelta(days=error_task_window_days)
print("XXX_", db.query(Task).filter(
or_(
and_(
Task.status == TaskStatus.SOLVED,
Task.creation_time <= solved_days_ago
),
and_(
Task.status == TaskStatus.ERROR,
Task.creation_time <= failed_days_ago
)
)
).delete(synchronize_session='fetch')) # was trying different flags


I have the following error (from python function)

(psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block\n\n[SQL: DELETE FROM tasks WHERE tasks.status = %(status_1)s AND tasks.creation_time <= %(creation_time_1)s OR tasks.status = %(status_2)s AND tasks.creation_time <= %(creation_time_2)s RETURNING tasks.id]\n[parameters: {'status_1': 'SOLVED', 'creation_time_1': datetime.datetime(2025, 8, 7, 8, 15, 40, 863536), 'status_2': 'ERROR', 'creation_time_2': datetime.datetime(2025, 8, 7, 8, 15, 40, 863541)}]\n(Background on this error at: https://sqlalche.me/e/20/2j85)"

2025-08-08 08:15:40.869 UTC [26] ERROR: current transaction is aborted, commands ignored until end of transaction block
2025-08-08 08:15:40.869 UTC [26] STATEMENT: DELETE FROM tasks WHERE tasks.status = 'SOLVED' AND tasks.creation_time <= '2025-08-07T08:15:40.867870'::timestamp OR tasks.status = 'ERROR' AND tasks.creation_time <= '2025-08-07T08:15:40.867877'::timestamp RETURNING tasks.id


Solution table looks like this:

dbname=# \d+ solutions
Table "public.solutions"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | character varying | | not null | | extended | |
task_id | character varying | | not null | |
...
Indexes:
"solutions_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"solutions_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id)

Continue reading...