Using pgAdmin and pgAgent with Greenplum

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.

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.

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.


Jon