Re: Using pgAdmin and pgAgent with Greenplum

Roberts, Jon wrote:
> 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.

Urgh.

> 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.

Yeah.

> 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?

I've never heard anyone ask before, but I don't think it's an
unreasonable request. It would be more simple to just add an additional
textbox to the SQL Step to allow a custom connection string to be
specified. If blank it works as now, otherwise it just uses whats there.

Anyone wanna pickup this mod?

/D