How to provide task parameters dynamically

[pavilalopes]

Hi, is there any way to set parameters dynamically for tasks like SendMail and CopyToFile?
We would like to set things like file names, email subjects, etc. from within the chain, instead of using fixed values.

We're evaluating pg_timetable to replace a bunch of ad-hoc shell scripts. I've been able to replicate everything the scripts are doing in pg_timetable, except this.

[pashagolub]

Hello. Yes, you can update timetable.parameter table on fly, e.g. in a previous step or by external chain.

For example, here we add new files to download for the task with task_id = 1 and we make set the file priorities through order_id

    INSERT INTO timetable.parameter (task_id, order_id, value)
        VALUES (1, 1, 
           '{
                "workersnum": 1,
                "fileurls": ["https://simplemaps.com/static/data/country-cities/mt/mt.csv"], 
                "destpath": "."
            }'::jsonb),
        (1, 2, 
           '{
                "workersnum": 5,
                "fileurls": ["https://foo.com/some-big-data.bin"], 
                "destpath": "."
            }'::jsonb);

[pashagolub]

You can check Mail sample and Download sample

[pavilalopes]

The download sample just has static parameters, but I took inspiration from the Mail sample that writes to the timetable.parameter table from inside a task.

So I created a test chain where the second task is a CopyToFile, and the first task is an SQL that updates the second task's parameters, like this:

update timetable.parameter
set value = jsonb_set(value, '{filename}', ('"/tmp/export ' || LOCALTIME || '.csv"')::jsonb)
where task_id = (
	select task_id from timetable.task
	where chain_id = current_setting('pg_timetable.current_chain_id')::bigint and task_name = 'export csv'
)

Unfortunately this didn't work. The update itself works fine, but the updated parameters are not picked up. The second task runs with the parameter values as they were before the update.

I guess the program is reading the parameters at the start of the chain execution, rather than at the start of the task execution? Anyway this seems to be a dead end.

[pashagolub]

Would you please make your inserting task autonomous = true.

The trick is every chain is executed inside it's own transaction, but system calls are done within main transaction. That's why changes you've made inside task are not visible for the main session.

If you make task autonomous it is executed outside of the chain transaction and results are available immediately.

[pavilalopes]

Yes, that works!

Makes sense in retrospect that timetable would perform its own operations and the chain's operations in different transactions.