Re: Using pgAdmin and pgAgent with Greenplum
От | Roberts, Jon |
---|---|
Тема | Re: Using pgAdmin and pgAgent with Greenplum |
Дата | |
Msg-id | 15362F202C62EA4590F5F3E5FA15021E05286791@nasappexc04.int.asurion.com обсуждение исходный текст |
Ответ на | Using pgAdmin and pgAgent with Greenplum ("Roberts, Jon" <Jon.Roberts@asurion.com>) |
Ответы |
Re: Using pgAdmin and pgAgent with Greenplum
(Dave Page <dpage@postgresql.org>)
|
Список | pgadmin-hackers |
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@postgresql.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.
В списке pgadmin-hackers по дате отправления:
Предыдущее
От: svn@pgadmin.orgДата:
Сообщение: SVN Commit by dpage: r6800 - in trunk/pgadmin3: . pgadmin/dlg