Re: Using pgAdmin and pgAgent with Greenplum

Roberts, Jon wrote:
> I am using pgAdmin with Greenplum and generally, it works very well.  It
> doesn't show the distribution of tables but that isn't a big deal.
>
> I now need a scheduling solution and pgAgent is the natural choice.  I first
> reviewed this and saw that the query that is launched by pgAdmin contains a
> correlated subquery which GP doesn't support.  Dave was nice enough to
> include a revised (and better performing) SQL statement that works with GP.
>
> Now I move on and actually start a daemon to execute a job.  The job runs
> but the status never changes from "Running".
>
> Looking more closely at pgagent.sql (which I only slightly modified to make
> it work with GP), I notice my oversight.  There are triggers on three tables
> and GP doesn't support triggers.

Urgh. I can see why, but still, urgh :-)

> I then looked at the code and see that in job.cpp, it has embedded SQL
> commands which then relies on database triggers.  So short of recompiling
> the C++ code, there isn't a way for me to fix it to work with GP.
>
> Ideally, the SQL commands found in job.cpp and pgAgent.cpp would not be
> there.  Instead, the C++ code would execute functions.  In other words,
> pgAdmin would focus on presentation and put the data logic in the database.

I think you're mixing up pgAdmin and pgAgent. The code you refer to is
all in pgAgent which doesn't do any presentation. All the pgAgent does
is query the jobs, log the results and cause the trigger to fire to
update the next run date. Yes, that could all be done through a pl/pgsql
API, however that does mean that it's easy to make changes that don't
cause the next run time to be updated (eg. if the admin updates the
tables manually). We could prevent that with appropriate rules, but I
guess GP doesn't support them either?

This mechanism would also make support more difficult as we'd need to
ensure that any time we ask someone to tweak a value that they also
manually make sure the next run date is updated.

In a nutshell, I'm not crazy about such a change unless a good, simple
API can be developed and we can arrange it such that the triggers are
still used for the non-GP case. I'm happy to look at any proposed
patches though.

> With this solution, it makes it possible to refactor the SQL code for job
> scheduling without having to recompile the executable.
>
> This is not only good for GP but also for those DBAs that want to add
> columns to the pgagent tables, add SQL hints, add more robust logging, etc
> but are unable to do it because the SQL is compiled in C++ rather than in
> the database.

We don't want them hacking anything about in there because we won't know
what they've broken when they ask for support.

Regards, Dave.