Skip site navigation (1) Skip section navigation (2)

Re: Using pgAdmin and pgAgent with Greenplum

From: Dave Page <dpage(at)postgresql(dot)org>
To: "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>
Cc: pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: Using pgAdmin and pgAgent with Greenplum
Date: 2007-10-31 09:50:39
Message-ID: 47284FEF.70103@postgresql.org (view raw or flat)
Thread:
Lists: pgadmin-hackers
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

In response to

pgadmin-hackers by date

Next:From: svnDate: 2007-10-31 09:57:01
Subject: SVN Commit by mha: r6803 - trunk/pgadmin3/pgadmin/utils
Previous:From: svnDate: 2007-10-31 09:13:03
Subject: SVN Commit by dpage: r6802 - branches/REL-1_8_0_PATCHES/pgadmin3/docs/en_US/hints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group