Обсуждение: 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;
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);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

Roberts, Jon wrote:
> In pgAgent.cpp, I would like to add LIMIT as shown below:
>
>
>   

[snip]

> What do you guys think?
>
>
>   

What has this to do with -hackers?

I don't even know what project this refers to - it certainly doesn't 
refer to core postgres, which is what -hackers is about.

cheers

andrew

> Roberts, Jon wrote:
> > In pgAgent.cpp, I would like to add LIMIT as shown below:
> >
> >
> >
>
> [snip]
>
> > What do you guys think?
> >
> >
> >
>
> What has this to do with -hackers?
>
> I don't even know what project this refers to - it certainly doesn't
> refer to core postgres, which is what -hackers is about.
>

pgAgent is the db job scheduler and I thought it was part of the core db
project.  The daemon for it is packaged with pgAdmin.


Jon

Roberts, Jon wrote:
> > Roberts, Jon wrote:
> > > In pgAgent.cpp, I would like to add LIMIT as shown below:

> > > What do you guys think?
> > 
> > What has this to do with -hackers?
> > 
> > I don't even know what project this refers to - it certainly doesn't
> > refer to core postgres, which is what -hackers is about.
> 
> pgAgent is the db job scheduler and I thought it was part of the core db
> project.  The daemon for it is packaged with pgAdmin.  

This is part of pgAdmin AFAIK.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Feb 26, 2008 at 08:10:09AM -0600, Roberts, Jon wrote:
> > Roberts, Jon wrote:
> > > In pgAgent.cpp, I would like to add LIMIT as shown below:
> > >
> > >
> > >
> > 
> > [snip]
> > 
> > > What do you guys think?
> > >
> > >
> > >
> > 
> > What has this to do with -hackers?
> > 
> > I don't even know what project this refers to - it certainly doesn't
> > refer to core postgres, which is what -hackers is about.
> > 
> 
> pgAgent is the db job scheduler and I thought it was part of the core db
> project.  The daemon for it is packaged with pgAdmin.  

Yeah, it's a part of the pgAdmin project. You'll want to direct your mails
to the pgadmin-hackers@postgresql.org list.

//Magnus

> -----Original Message-----
> From: Magnus Hagander [mailto:magnus@hagander.net]
> Sent: Tuesday, February 26, 2008 8:17 AM
> To: Roberts, Jon
> Cc: Andrew Dunstan; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] pgAgent job limit
>
> On Tue, Feb 26, 2008 at 08:10:09AM -0600, Roberts, Jon wrote:
> > > Roberts, Jon wrote:
> > > > In pgAgent.cpp, I would like to add LIMIT as shown below:
> > > >
> > > >
> > > >
> > >
> > > [snip]
> > >
> > > > What do you guys think?
> > > >
> > > >
> > > >
> > >
> > > What has this to do with -hackers?
> > >
> > > I don't even know what project this refers to - it certainly
doesn't
> > > refer to core postgres, which is what -hackers is about.
> > >
> >
> > pgAgent is the db job scheduler and I thought it was part of the
core db
> > project.  The daemon for it is packaged with pgAdmin.
>
> Yeah, it's a part of the pgAdmin project. You'll want to direct your
mails
> to the pgadmin-hackers@postgresql.org list.
>
> //Magnus

Thanks guys.  Sorry about that.  I guess I'm still think of Jobs how
Oracle does it which is part of the core database.



Jon