Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Automatically unlock tasks that have been locked for more than 3 days #1603

Open
spwoodcock opened this issue Jun 27, 2024 · 4 comments · May be fixed by #1984
Open

Automatically unlock tasks that have been locked for more than 3 days #1603

spwoodcock opened this issue Jun 27, 2024 · 4 comments · May be fixed by #1984
Assignees
@spwoodcock @Anuj-Gupta4
Labels
backend Related to backend code effort:medium Likely a day or two enhancement New feature or request priority:low Backlog of tasks that will be addressed in time

Comments

@spwoodcock
Copy link
Member

spwoodcock commented Jun 27, 2024 •
edited
Loading

Blocker #1604

Is your feature request related to a problem? Please describe.

  • A task may be locked by a user, then they don't actually map, or forget to unlock.
  • The project admin can unlock the task, so this is not a complete blocker.
  • But other mappers cannot unlock the task.

Describe the solution you'd like

  • Automatically unlock the task on the database after 3 days have elapsed.

Solution

This is a relatively easy solution via pg_cron directly on the database.

Create a function:

CREATE OR REPLACE FUNCTION unlock_longstanding_locked_tasks()
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    svc_user_id INT;
BEGIN
    SELECT id INTO svc_user_id
    FROM users
    WHERE username = 'svcfmtm';

    INSERT INTO task_history (project_id, task_id, action, action_date, user_id)
    SELECT project_id, task_id, 'READY', NOW(), svc_user_id
    FROM (
        SELECT project_id, task_id, action, action_date,
               ROW_NUMBER() OVER (PARTITION BY project_id, task_id ORDER BY action_date DESC) as row_num
        FROM task_history
    ) subquery
    WHERE subquery.row_num = 1
      AND subquery.action = 'LOCKED_FOR_MAPPING'
      AND current_timestamp - subquery.action_date >= interval '3 days';
END;
$$;

Create the cron (runs every day at midnight):

SELECT cron.schedule('0 0 * * *', 'SELECT unlock_longstanding_locked_tasks();');
@spwoodcock spwoodcock added enhancement New feature or request backend Related to backend code labels Jun 27, 2024
@spwoodcock spwoodcock self-assigned this Jun 27, 2024
@ramyaragupathy ramyaragupathy added the priority:low Backlog of tasks that will be addressed in time label Jun 27, 2024
@spwoodcock spwoodcock added the effort:medium Likely a day or two label Oct 6, 2024
@spwoodcock
Copy link
Member Author

The logic could be better here:

  • Now we have odk_entities table we can check for any status updates within the task area, I.e. if progress is made.

  • As a manager could assign a task (lock it for a user and notify them), we should account for this. If the latest event is ASSIGN, then unlock if it was three days ago.

  • But if the latest event was MAP and there has been no progress, then unlock within an three hours.

  • Perhaps there is a way to trigger this during an endpoint call, instead of as a cron (then this handles the issue of only wanting to run on active projects too).

@manjitapandey
Copy link
Contributor

I agree with your idea of checking the last activity but i guess only 3 hours will be less for the users. We can implement the logic if there is room to increase the timeout easily in future.

@Anuj-Gupta4
Copy link
Contributor

@spwoodcock This task seems better suited for cron. Is there an API suitable for triggering this task in the background?

@spwoodcock
Copy link
Member Author

spwoodcock commented Dec 6, 2024 •
edited
Loading

My original reply

A scheduled event does make sense: but it should be sure to only run on active projects, with events perhaps in the last week.

There is no API to do this - the code has to be written, so I would suggest the SQL approach, as its as close to the data as possible.

Cron on a Linux system is not very portable when it comes to deployment, especially in distributed systems such as Kubernetes. A pg_cron function seems the most versatile and efficient approach to me. Particularly as its guaranteed to only run once, and show have minimal performance overhead. We could also run maintenance tasks like VACUUM.

The only hurdle is actually installing pg_cron into the database container, for which I should probably help out:

https://www.nico.fyi/blog/dockerfile-for-pgcron-postgres-cron-job
https://eduanbekker.com/post/pg-partman/

My reconsidered reply

Software development comes with trade offs.
While using pg_cron does seem like objectively the optimal solution, it would result in:

  • Moving away from official postgis containers to a custom build, adding a small maintenance burden to keep the build pipeline in place.
  • Increased complexity of using a Postgres trigger, function, and cron (which are more advanced Postgres features).

So with this in mind, to make our codebase as approachable and understandable by all developers as possible, we can probably just write the script in Python.

The docker compose config can have a container for running system cron that I can set up once the script is in place and tested 👍

@Anuj-Gupta4 Anuj-Gupta4 linked a pull request Dec 12, 2024 that will close this issue
Open
7 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@spwoodcock spwoodcock

@Anuj-Gupta4 Anuj-Gupta4

Labels
backend Related to backend code effort:medium Likely a day or two enhancement New feature or request priority:low Backlog of tasks that will be addressed in time
Projects
Field Mapping Tasking Manager (FMTM)
Status: Tasks
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

feat: auto unlock locked tasks after 3 days Anuj-Gupta4/fmtm
4 participants
@ramyaragupathy @spwoodcock @Anuj-Gupta4 @manjitapandey