Coordinated Interval Chain Execution across multiple clients

[radim]

Is your feature request related to a problem? Please describe.
The current behavior of pg_timetable supports non-cron syntax like @after. However, when implementing interval chains, I noticed that they are executed on all clients unless the chain is specifically bound to a particular client. This results in redundant execution across multiple clients.

While there's active_chain table to control scheduler concurrency, it does not provide support for this particular case (as the records are deleted).

Describe the solution you'd like
To improve scalability, it might be helpful to extend the coordination mechanism to specifically support interval tasks within the timetable schema. Introducing new logic/altering existing active_chain logic would solve this and allow coordinated execution of those chains in scalable environment (including dynamically created clients).

Describe alternatives you've considered
As mentioned above the 'workaround' is to use client affinity, but in that case the orchestration of the workload would be outside the pg_timetable.

---

I would like to check what do you think about possible solutions. Either extending action_chain to support UPSERT like behaviour together with active (true/false), instead of INSERT/DELETE as it is at the moment. Or whatever it would be better to come up other mechanism.

[pashagolub]

Hello.

Would you please provide an example of what you are trying to achieve. I cannot understand the problem

[pashagolub]

If you want to limit the number of instances that this chain may have running at the same time, just set max_instances to 1

[radim]

@pashagolub example is straightforward. You can try chaining with @after 30 seconds, @after 1 minute, or similar. If you don’t assign the chain to a specific client, the evaluation will occur on all available clients. The max_instance setting only prevents simultaneous execution if the job runs at the exact same time.

Simultaneous execution occurs because individual clients start at different times, leading to slightly different evaluation times. I understand this is an edge case, so if you believe it falls outside the scope of pg_timetable, that’s also a valid answer. I’m trying to determine whether it’s worth the effort (I’m happy to work on a PR) to adjust this behavior.

In my particular case, I attempted to use pg_timetable to orchestrate a long-running data migration (e.g., executing smaller data modification batches repeatedly). In this context, @after is a perfect fit.

[pashagolub]

Interval chains explicitly are designed with the idea of the client independence. That means client itself controls when the next execution is started. If we are talking about several pg_timetable clients that needs to be synchronized the simplest way would be to switch to regular cron with least common interval.

Another solution might be to check timetable.execution_log.finished value for chain as a first task and decide if we want to proceed.

DO $$
BEGIN
	IF now() - max(finished) < '30 seconds' FROM timetable.timetable.execution_log el WHERE chain_id = 1 THEN
		RAISE EXCEPTION 'aaaah, too early!';
	END IF; 
END;$$

Set ignore_error for this task to TRUE and set max_instances to 1 for the chain. That will

• make sure only one chain is runable at the time;
• each new run will check the latest finish time and will skip execution.

Sounds good?

[radim]

The explanation that “chains are explicitly designed with the idea of client independence” clarifies the approach. I initially thought of it the other way around, with the database handling orchestration and clients being responsible only for execution. The provided check on timetable.execution_log.finished is a good solution, but I will use RETURN instead of RAISE, as we still rely on error handling.

Thanks for the explanation @pashagolub 👍🏻

[pashagolub]

You're welcome! Only cron chains are orchestrated in the database, because we want database time and timezone. But since pg_timetable instances may come and go in dynamic environments, I decided to make interval chains independent of database connection.