SEO

I have two jobs scheduled and the second one ran every poll interval,
regardless of any schedule I put in it. I traced the cause to the "host
agent" field being populated.

Looking at the source of pgAgent.cpp, from 5 weeks ago, I see the
following query being used to check if the agent should run the job:

*while* (1)
{
*bool* foundJobToExecute=false;

LogMessage(_(*"Checking for jobs to run"*), LOG_DEBUG);
DBresult *res=serviceConn->Execute(
wxT(*"SELECT J.jobid "*)
wxT(*" FROM pgagent.pga_job J "*)
wxT(*" WHERE jobenabled "*)
wxT(*" AND jobagentid IS NULL "*)
wxT(*" AND jobnextrun <= now() "*)
wxT(*" AND jobhostagent = '' OR jobhostagent = '"*) + hostname + wxT(*"'"*)
wxT(*" ORDER BY jobnextrun"*));

*if* (res)


......


The SQL will return a row if the jobhostagent field matches every time (the OR clause is on the same level as all the AND clauses). We need to group the last two clauses:

*while* (1)
{
*bool* foundJobToExecute=false;

LogMessage(_(*"Checking for jobs to run"*), LOG_DEBUG);
DBresult *res=serviceConn->Execute(
wxT(*"SELECT J.jobid "*)
wxT(*" FROM pgagent.pga_job J "*)
wxT(*" WHERE jobenabled "*)
wxT(*" AND jobagentid IS NULL "*)
wxT(*" AND jobnextrun <= now() "*)
wxT(*" AND (jobhostagent = '' OR jobhostagent = '"*) + hostname + wxT(*"')"*)
wxT(*" ORDER BY jobnextrun"*));

*if* (res)




......


this bug would be reproduced by populating the host agent field of any
job with the full dns name of your server running pgagent. Look at the
debug log of pgagent and you'll see it run that job every poll
interval. I don't have a build environment to test this with, but I'm
sure the change I'm recommending to the SQL will fix this bug.

Thanks,
Brian Kalbfus

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Brian Kalbfus wrote:
>
> The SQL will return a row if the jobhostagent field matches every time
> (the OR clause is on the same level as all the AND clauses). We need to
> group the last two clauses:

Thanks Brian, I've applied a patch per your suggestion.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq