Re: FW: pgAgent job limit

Dave,

If we could get pgagent to compile we would make the change in our local
copy and deploy it here. We are having a really rough time compiling
pgagent, atleast on our dev boxes. The problem seems to be around
manifest files and that the compiler seems to want to include
"Microsoft.VC90.DebugCRT" which is the visual studio 2008 crt dll. This
is odd because I am compiling it in 2005, but I also have 2008
installed. And when I compile in 2008 I get errors that it cant find
MSVCRT90D.dll and when I copy the file into the pgagent folder I get
other errors because its trying to pull in a dll that's not in the
manifest file.

Here is the manifest generated when I try to compile in VS2008 (you can
see its referncing the same assembly twice):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1"
manifestVersion="1.0">
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
<security>
<requestedPrivileges>
<requestedExecutionLevel level="asInvoker"
uiAccess="false"></requestedExecutionLevel>
</requestedPrivileges>
</security>
</trustInfo>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.VC90.DebugCRT"
version="9.0.21022.8" processorArchitecture="x86"
publicKeyToken="1fc8b3b9a1e18e3b"></assemblyIdentity>
</dependentAssembly>
</dependency>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32"
name="Microsoft.Windows.Common-Controls" version="6.0.0.0"
processorArchitecture="x86" publicKeyToken="6595b64144ccf1df"
language="*"></assemblyIdentity>
</dependentAssembly>
</dependency>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.VC90.DebugCRT"
version="9.0.20706.1" processorArchitecture="x86"
publicKeyToken="1fc8b3b9a1e18e3b"></assemblyIdentity>
</dependentAssembly>
</dependency>
</assembly>

-----Original Message-----
From: Dave Page [mailto:dpage(at)pgadmin(dot)org]
Sent: Tuesday, February 26, 2008 4:14 PM
To: Roberts, Jon; pgadmin-hackers
Cc: Lucas, Craig
Subject: Re: FW: [pgadmin-hackers] 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