SVN Commit by dpage: r4240 - trunk/pgadmin3/xtra/pgagent - Mailing list pgadmin-hackers

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=failedstopping job, i=ignored failure'; 
+COMMENT ON COLUMN pgagent.pga_jobsteplog.jslstatus IS 'Status of job step: r=running, s=successfully finished,
f=failedstopping job, i=ignored failure, d=aborted'; 
 COMMENT ON COLUMN pgagent.pga_jobsteplog.jslresult IS 'Return code of job step';