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.