Potential issue with pgAgent when updating pga_jobsteplog

["Benjamin Krajmalnik" <kraj(at)illumen(dot)com>]

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.