Обсуждение: Potential issue with pgAgent when updating pga_jobsteplog
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.
On Mon, May 5, 2008 at 4:57 PM, Benjamin Krajmalnik <kraj@illumen.com> wrote: > > 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. I've gone with the second option and increased the jslresult column to be an int4 (matching the C++ code). You should be able to increase the column size on your server as no other code changes are required. Thanks, Dave -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Via pgAdmin, going into the pgagent catalog, I do not have an option of changing the column type to int4. > -----Original Message----- > From: Dave Page [mailto:dpage@pgadmin.org] > Sent: Tuesday, May 06, 2008 4:57 AM > To: Benjamin Krajmalnik > Cc: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Potential issue with pgAgent > when updating pga_jobsteplog > > On Mon, May 5, 2008 at 4:57 PM, Benjamin Krajmalnik > <kraj@illumen.com> wrote: > > > > 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. > > I've gone with the second option and increased the jslresult > column to be an int4 (matching the C++ code). You should be > able to increase the column size on your server as no other > code changes are required. > > Thanks, Dave > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
No, because pgadmin treats it as a system catalog. You'll need to use ALTER TABLE in the query tool. On 5/6/08, Benjamin Krajmalnik <kraj@illumen.com> wrote: > Via pgAdmin, going into the pgagent catalog, I do not have an option of > changing the column type to int4. > > > > -----Original Message----- > > From: Dave Page [mailto:dpage@pgadmin.org] > > Sent: Tuesday, May 06, 2008 4:57 AM > > To: Benjamin Krajmalnik > > Cc: pgadmin-support@postgresql.org > > Subject: Re: [pgadmin-support] Potential issue with pgAgent > > when updating pga_jobsteplog > > > > On Mon, May 5, 2008 at 4:57 PM, Benjamin Krajmalnik > > <kraj@illumen.com> wrote: > > > > > > 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. > > > > I've gone with the second option and increased the jslresult > > column to be an int4 (matching the C++ code). You should be > > able to increase the column size on your server as no other > > code changes are required. > > > > Thanks, Dave > > > > -- > > Dave Page > > EnterpriseDB UK: http://www.enterprisedb.com > > > -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, May 6, 2008 at 11:11 PM, Benjamin Krajmalnik <kraj@illumen.com> wrote: > What query should I issue to change this. Not sure since it is a system > catalog. It's not really a catalog - it's only pgAdmin that treats it as one. Something like this (untested) should do: ALTER TABLE pgagent.pga_jobsteplog ALTER COLUMN jslresult TYPE int4; -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com