pgAgent job limit

From: "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>
To: <pgadmin-hackers(at)postgresql(dot)org>
Subject: pgAgent job limit
Date: 2008-02-26 14:14:03
Message-ID: 1A6E6D554222284AB25ABE3229A92762715658@nrtexcus702.int.asurion.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers

In pgAgent.cpp, I would like to add LIMIT as shown below:

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")
wxT(" LIMIT pgagent.pga_job_limit('") + hostname + wxT("')"));

This requires two new objects:
create table pgagent.pga_job_throttle (jobmax int);

insert into pgagent.pga_job_throttle values (2);

create or replace function pgagent.pga_job_limit(p_hostname varchar)
returns int as
$$
declare
v_limit int;
v_running int;
begin

select jobmax
into v_limit
from pgagent.pga_job_throttle;

if v_limit < 0 or v_limit is null then
select count(*)
into v_limit
from pgagent.pga_job j
where jobenabled
and jobagentid is null
and jobnextrun <= now()
and (jobhostagent = '' or jobhostagent = p_hostname);
else
select count(*)
into v_running
from pgagent.pga_job j
join pgagent.pga_joblog l
on j.jobid = l.jlgjobid
where j.jobenabled
and (j.jobhostagent = '' or j.jobhostagent = p_hostname)
and l.jlgstatus = 'r'; --Status of job: r=running,
s=successfully finished, f=failed, i=no steps to execute, d=aborted

v_limit := v_limit - v_running;

end if;

return v_limit;

end;
$$
language 'plpgsql';

This function allow pgAgent to be throttled dynamically by managing the
pgagent.pga_job_throttle table. If you want to disable all jobs from
running, you set the value to 0. If you want to let as many jobs run at
once (like the default) to run at a time, you either delete the record
from the table or you can set the value to a negative number.

pgAgent scales much better without having excessive number of
connections to the database with one line change to the C++ code.

What do you guys think?

Jon

Browse pgadmin-hackers by date

  From Date Subject
Next Message Otto Brito 2008-02-26 15:14:27 Access to Postgresql
Previous Message svn 2008-02-26 11:55:23 SVN Commit by dpage: r7094 - branches/REL-1_8_0_PATCHES/pgadmin3/pgadmin/schema