Re: Using pgAdmin and pgAgent with Greenplum

I found more problems when trying to use GP. For instance, this update
statement will not work in GP.

UPDATE pgagent.pga_jobsteplog SET jslstatus='d'
WHERE jslid IN SELECT jslid
FROM pga_tmp_zombies z, pgagent.pga_job j, pgagent.pga_joblog l,
pgagent.pga_jobsteplog s
WHERE z.jagpid=j.jobagentid AND j.jobid = l.jlgjobid
AND l.jlgid = s.jsljlgid AND s.jslstatus='r'

This code is found in pgAgent.cpp.

Updates can only happen like this when the updated column is distributed by
the same column as the source. I created a for loop to get around this for
testing and it took 30 seconds to complete.

So, I think the best solution is to use PostgreSQL as the database server to
hold my jobs. I was able to use a database link to execute a function in GP
without any problems. Using a batch step also works but we have to execute
psql with the host, database, and sql command specified in the script for
each step which is error prone.

However, to make pgAgent and pgAdmin work better with my solution, it would
be nice to have an enhancement. The enhancement could also benefit others
wanting to separate the database server where jobs are maintained from the
target server(s) where sql should be executed.

On the screen where you define a SQL step, add another parameter for Server.
When executing the SQL defined, pgAgent would connect to the Server and then
execute the SQL defined in the database specified in the step.

This solution would require an additional screen to define the servers too.

Is there much demand for allowing a SQL job step to be executed on a remote
server and database?

Jon
> -----Original Message-----
> From: Dave Page [mailto:dpage(at)postgresql(dot)org]
> Sent: Monday, October 29, 2007 8:53 AM
> To: Roberts, Jon
> Cc: pgadmin-hackers
> Subject: Re: [pgadmin-hackers] Using pgAdmin and pgAgent with Greenplum
>
> Roberts, Jon wrote:
> > I've looked at this code all day long and tried many hacks to make it
> work
> > with GP but there isn't a way.
> >
> > The easiest way I can think of to handle this with the least amount of
> > change to the architecture is to add another column to pg_job. Maybe a
> > Boolean called jobcompleted. Then create a view called vw_pg_job which
> > executes the function pga_next_schedule that returns the jobnextrun
> value.
>
> I'm not sure why you'd need the extra column, but there are some issues
> with using a view to replace the nextrun column:
>
> - pgAgent queries each job every minute to see if it needs to run. That
> could lead to a lot of cpu being used on that rather nasty pl/pgsql
> function.
>
> - When we spec'ed pgAgent, it was decided that if a schedule were
> missed, that instance should run immediately on startup. The proposed
> change would prevent that behaviour, though personally I'm not sure
> that's necessarily a bad thing.
>
> - The 'Run Now' feature in pgAdmin would need to be reworked - perhaps
> by adding a flag to pg_job to indicate the job should run once
> regardless of scheduling.
>
> The second are more minor issues, but the first doesn't seem at all good
> to me.
>
> Regards, Dave.