late tasks management on server recovery

[larrieu-olivier]

hello

I'm doing a validation to use "prefect version 3" for our use cases.

the desired architecture is a "prefect" server with one worker per "workpool".
the workers will be instances managed by systemd and the server will be managed by "docker".

I would like to know what is the best approach to handle a server outage.
When restarting the server I would like to cancel all tasks whose status is "Late" and whose schedule date is greater than 3 hours for exemple.

I especially don't want tasks in "late" status to be replayed by the workers when the server restarts, this could create unnecessary processes.

I tried to manage it with "automations" but that doesn't completely solve the problem.
What would you advise me to manage a server failure return without replaying processes unnecessarily?

thanking you in advance.

[zzstoatzz]

hi @larrieu-olivier - thanks for the discussion and good question!

your proposed setup makes sense to me.

When restarting the server I would like to cancel all tasks whose status is "Late" and whose schedule date is greater than 3 hours for example.

my initial reaction was to use an automation to delete Late flow runs after some time interval but I realize you might undesirably replay Late runs at startup if the automation doesn't fire before your workers pick up some runs, which is maybe what you were referring to here:

I tried to manage it with "automations" but that doesn't completely solve the problem.

my first (heavy-handed) instinct would be to have a setup script that runs before prefect server start with something like

DELETE FROM flow_runs 
WHERE state_type = 'SCHEDULED' 
  AND state_name = 'Late'
  AND next_scheduled_start_time < NOW() - INTERVAL '3 hours';

but perhaps we ought to allow some hook at server startup time for cleanup like this in a more first-class way

[larrieu-olivier]

hi @zzstoatzz, thank you for your quick response,

I tried two approaches, the first directly in python but I don't really like it and the sql approach as you propose and which I find much cleaner in this case.

I wasn't sure about the SQL script solution but I think I'll do it like this, an init script when starting Postgres.
I have looked at database schema and see than there is a foreign key on flow_run_state,
I would prefer to change the status of the flows to "cancel" with a message rather than deleting them.
This will make it easier to understand the history of runs and retain the possibility of manually replaying a task if necessary.
Is there a subtlety to take into account to modify the status of a flow_run in SQL due to the foreign key on flow_run_state ?

thank you very much for taking the time to answer my question.

regards