I have a scehduled job which randomizes a column inside a table.
The query is as follows:
update tbllocations set randsort=random();
Every time the job runs, it performs its task properly without any issues, but I kept seeing the job as failed, and if you view the statistics on the jon it shous as running.
Checking the PostgreSQL log file, I found the reason why it is showing as still running.
The query which updates pga_jobsteplog is failing.
The query which is being issued is:
UPDATE pgagent.pga_jobsteplog SET jslduration=now()-jslstart, jslresult=81961, jslstatus='s', jsloutput='' WHERE jslid=2890
The error being generated is "smallint out of range".
Apparently the jslresult column is a smallint, and the query is returning the number of rows affected.
I believe one of 2 things probably needs to be done:
1. Update the documentation to mention what range the result needs to be. This means that queries which affect many rows need to be encapsulated in a stored procedure or have a supplementary query such as "select 1" added to the step in order to return an in-range value
2. Change jslresult to a data type which will accomodate a larger value.
