json: cannot unmarshal object into Go value of type []interface {}

[ashx1901]

Hi ,

I would be grateful if you could provide me the solution for this error "json:
cannot unmarshal object into Go value of type []interface {}" Below is my code.

INSERT INTO timetable.parameter (task_id, order_id, value)
        VALUES (1, 1,
        '{"username": "[email protected]"
         ,"serverhost": "smtp.abc.com"
         ,"senderaddr": "[email protected]"
         ,"serverport": 25
         ,"toaddr":["[email protected]"]
         ,"subject": "Job completed Successfully."}
        '::jsonb);

[pashagolub]

Hello,

I cannot reproduce the error. Would you please provide us with the output of
./pg_timetable --log-level=debug --version --clientname=worker001 postgresql://scheduler@localhost:5432/timetable
command, where the last URL is the URL to your database and clientname is specific to your environment

Thanks in advance!

[ashx1901]

Hi,

I have attached the logs here for your refrence. Could you please suggest what i am missing here to fix this error.

Pgtimetable.log

[pashagolub]

I cannot reproduce the error. I've checked on the very same version and the database schema as you.

So either you provided the wrong json or the problem is not in the json.

log.txt

[pashagolub]

also this string in the logs looks very suspicious [client:lientname=worker1]

[pashagolub]

From these lines I see you're trying to execute remote SQL task and it is the reason for the error:

2022-09-22 06:24:46.810 [INFO] [chain:1] [task:1] Remote connection established... (pgengine/transaction.go:204 pgengine.(*PgEngine).GetRemoteDBTransaction)
2022-09-22 06:24:46.810 [DEBUG] [chain:1] [task:1] [sql:begin] [args:[]] [commandTag:BEGIN] [pid:4135334] [time:114.286µs] Exec
2022-09-22 06:24:46.810 [DEBUG] [chain:1] [task:1] [sql:rollback] [args:[]] [commandTag:ROLLBACK] [pid:4135334] [time:87.66µs] Exec
2022-09-22 06:24:46.811 [INFO] [chain:1] [task:1] Closing remote session (pgengine/transaction.go:216 pgengine.(*PgEngine).FinalizeRemoteDBConnection)
2022-09-22 06:24:46.811 [DEBUG] [chain:1] [task:1] [pid:4135334] closed connection
2022-09-22 06:24:46.811 [ERROR] [chain:1] [task:1] [error:json: cannot unmarshal object into Go value of type []interface {}] Task execution failed (scheduler/chain.go:258 scheduler.(*Scheduler).executeСhainElement)

[pashagolub]

And that is probably because you're trying to pass parameters in a wrong format.
SQL task expects an array, e.g.

        '[ "one", 2, 3.14, false ]'::jsonb

[ashx1901]

I don't know from where it is taking [client:lientname=worked1] but I can confirm that I am using the below command in the service file and running this procedure from the right schema. Jason code which i shared with you in my above comment is the same code i am using in my task don't know why it is throwing up this error.

pg_timetable -h localhost -p 5432 --log-level=debug --version -d pg_timetable -u scheduler -clientname=worker1 --password=******

[ashx1901]

My scenario is I want to call up a stored procedure and I want to get notified on completion. So this is why I am adding email parameters here. If possible could you just give an example how can we call a stored procedure and get email notification on its completion.

[pashagolub]

ah, now I see!

So you need a chain of two tasks:

• sql task to call the procedure
• built-in email task to notify the user (example https://github.com/cybertec-postgresql/pg_timetable/blob/master/samples/Mail.sql)

So the problem is you are using only one task expecting that it will send the email. No, sending email is the standalone built-in task which must be configured

[ashx1901]

Hi,

With the help of your above comment, i am able to pass the failing error of "cannot unmarshal ..." but now I am getting "unexpected end of JSON". Please refer to the below code. Thanks

DO $$
DECLARE
v_head_id bigint;
v_task_id bigint;
v_chain_id bigint;
BEGIN
-- Create the chain with default values executed every minute (NULL == '* * * * *' :: timetable.cron)
INSERT INTO timetable.chain (chain_name, live, run_at)
VALUES ('old_am_data_clean_up5', TRUE,'31 20 * * *')
RETURNING chain_id INTO v_chain_id;

-- Step 1. Run Daily Capture Script
-- Create the chain
INSERT INTO timetable.task (chain_id, task_order, run_as,kind, command,database_connection, ignore_error,autonomous)
VALUES (v_chain_id, 2,'am_owner','SQL','CALL am.old_am_data_clean_up()','host=10.0.20.10.abc.com user=am_owner password=******** dbname=dev_1_db port=5432', TRUE, TRUE)
RETURNING task_id INTO v_task_id;
END;
$$ LANGUAGE PLPGSQL;

chain_ID of this task is 38 and task_id is 29 based on these values I added another task to notify the user

INSERT INTO timetable.task (chain_id, task_order, kind, command)
VALUES (38, 2, 'BUILTIN', 'SendMail')

INSERT INTO timetable.parameter (task_id, order_id, value)
VALUES (29, 1,
'{"username": "postgres@[email protected]"
,"serverhost": "smtp.abc.com"
,"senderaddr": "postgres@[email protected]"
,"serverport": 25
,"toaddr":["[email protected]"]
,"subject": "test Job completed Successfully."
}'::jsonb);

I

[ashx1901]

Never mind I have fixed it. The problem was i was putting wrong task_id in my email parameters. You may close this issue. Thank you very much for your support

[pashagolub]

You're more than welcome!