Re: FW: pgAgent job limit

Jon,

I'm pretty busy at the moment so haven't had a chance to review this
properly, but a couple of things spring instantly to mind - basically
you are currently proposing a hack and not a full solution - your 1
line of code is merely the tip of the iceberg. In addition to that and
the script changes you note, we'd also need documentation and UI
support.

The alternative approach would be to make it a command line option to
pgagent. That wouldn't require any UI support of course.

Regards, Dave.

On Tue, Feb 26, 2008 at 9:47 PM, Roberts, Jon <Jon(dot)Roberts(at)asurion(dot)com> wrote:
> Dave, we have been spending all day trying to get MS Visual C++ to work
> with wxWidgets and compile pgAgent all for one line of code to change.
>
> Do you think the below change is a good idea and if so, could you add
> this to speed up the deployment?
>
> I also think we need to add the DDL changes to pgagent.sql as well as
> recompile pgagent.exe.
>
>
> Thanks!
>
>
> Jon
>
>
> -----Original Message-----
> From: pgadmin-hackers-owner(at)postgresql(dot)org
> [mailto:pgadmin-hackers-owner(at)postgresql(dot)org] On Behalf Of Roberts, Jon
> Sent: Tuesday, February 26, 2008 8:14 AM
> To: pgadmin-hackers(at)postgresql(dot)org
> Subject: [pgadmin-hackers] pgAgent job limit
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
>
>
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Oracle-compatible database company