SVN Commit by dpage: r4240 - trunk/pgadmin3/xtra/pgagent

Author: dpage
Date: 2005-05-23 20:57:51 +0100 (Mon, 23 May 2005)
New Revision: 4240

Modified:
trunk/pgadmin3/xtra/pgagent/pgAgent.cpp
trunk/pgadmin3/xtra/pgagent/pgagent.sql
Log:
Cleanup zombies more effectively.

Modified: trunk/pgadmin3/xtra/pgagent/pgAgent.cpp
===================================================================
--- trunk/pgadmin3/xtra/pgagent/pgAgent.cpp 2005-05-23 18:58:42 UTC (rev 4239)
+++ trunk/pgadmin3/xtra/pgagent/pgAgent.cpp 2005-05-23 19:57:51 UTC (rev 4240)
@@ -51,15 +51,18 @@
// There are orphaned agent entries
// mark the jobs as aborted
rc=serviceConn->ExecuteVoid(
- wxT("UPDATE pgagent.pga_joblog SET jlgstatus='d' ")
- wxT(" FROM pga_tmp_zombies Z ")
- wxT(" JOIN pgagent.pga_job J ON jobagentid=jagpid ")
- wxT(" JOIN pgagent.pga_joblog LG ON jlgjobid=J.jobid ")
- wxT(" WHERE LG.jlgstatus='r';\n")
+ wxT("UPDATE pgagent.pga_joblog SET jlgstatus='d' WHERE jlgid IN (")
+ wxT("SELECT jlgid ")
+ wxT("FROM pga_tmp_zombies z, pgagent.pga_job j, pgagent.pga_joblog l ")
+ wxT("WHERE z.jagpid=j.jobagentid AND j.jobid = l.jlgjobid AND l.jlgstatus='r');\n")

+ wxT("UPDATE pgagent.pga_jobsteplog SET jslstatus='d' WHERE jslid IN ( ")
+ wxT("SELECT jslid ")
+ wxT("FROM pga_tmp_zombies z, pgagent.pga_job j, pgagent.pga_joblog l, pgagent.pga_jobsteplog s ")
+ wxT("WHERE z.jagpid=j.jobagentid AND j.jobid = l.jlgjobid AND l.jlgid = s.jsljlgid AND s.jslstatus='r');\n")
+
wxT("UPDATE pgagent.pga_job SET jobagentid=NULL, jobnextrun=NULL ")
- wxT(" FROM pga_tmp_zombies Z ")
- wxT(" JOIN pgagent.pga_job J ON jobagentid=jagpid;\n")
+ wxT(" WHERE jobagentid IN (SELECT jagpid FROM pga_tmp_zombies);\n")

wxT("DELETE FROM pgagent.pga_jobagent ")
wxT(" WHERE jagpid IN (SELECT jagpid FROM pga_tmp_zombies);\n")

Modified: trunk/pgadmin3/xtra/pgagent/pgagent.sql
===================================================================
--- trunk/pgadmin3/xtra/pgagent/pgagent.sql 2005-05-23 18:58:42 UTC (rev 4239)
+++ trunk/pgadmin3/xtra/pgagent/pgagent.sql 2005-05-23 19:57:51 UTC (rev 4240)
@@ -128,7 +128,7 @@
jslid serial NOT NULL PRIMARY KEY,
jsljlgid int4 NOT NULL REFERENCES pgagent.pga_joblog (jlgid) ON DELETE CASCADE ON UPDATE RESTRICT,
jsljstid int4 NOT NULL REFERENCES pgagent.pga_jobstep (jstid) ON DELETE CASCADE ON UPDATE RESTRICT,
-jslstatus char NOT NULL CHECK (jslstatus IN ('r', 's', 'i', 'f')) DEFAULT 'r', -- running, success, ignored, failed
+jslstatus char NOT NULL CHECK (jslstatus IN ('r', 's', 'i', 'f', 'd')) DEFAULT 'r', -- running, success, ignored, failed, aborted
jslresult int2 NULL,
jslstart timestamptz NOT NULL DEFAULT current_timestamp,
jslduration interval NULL,
@@ -136,7 +136,7 @@
) WITHOUT OIDS;
CREATE INDEX pga_jobsteplog_jslid ON pgagent.pga_jobsteplog(jsljlgid);
COMMENT ON TABLE pgagent.pga_jobsteplog IS 'Job step run logs.';
-COMMENT ON COLUMN pgagent.pga_jobsteplog.jslstatus IS 'Status of job step: r=running, s=successfully finished, f=failed stopping job, i=ignored failure';
+COMMENT ON COLUMN pgagent.pga_jobsteplog.jslstatus IS 'Status of job step: r=running, s=successfully finished, f=failed stopping job, i=ignored failure, d=aborted';
COMMENT ON COLUMN pgagent.pga_jobsteplog.jslresult IS 'Return code of job step';