V10__Support_multiple_GPU.sql.diff

--- V1__Initial_schema.sql.txt	2021-02-19 19:40:43.000000000 -0800
+++ V10__Support_multiple_GPU.sql	2021-02-19 21:53:11.000000000 -0800
+DROP VIEW vs_show_resource;
+CREATE VIEW vs_show_resource (pk_show, int_cores, int_gpus) AS
   SELECT
         job.pk_show,
-        SUM(int_cores) AS int_cores
+        SUM(int_cores) AS int_cores, SUM(int_gpus) AS int_gpus
     FROM
        job,
        job_resource
@@ -14,12 +212,13 @@ CREATE VIEW vs_show_resource (pk_show, i
        job.pk_show;
 
 
-
-CREATE VIEW vs_job_resource (pk_job, int_procs, int_cores, int_mem_reserved) AS
+DROP VIEW vs_job_resource;
+CREATE VIEW vs_job_resource (pk_job, int_procs, int_cores, int_gpus, int_mem_reserved) AS
   SELECT
        job.pk_job,
        COUNT(proc.pk_proc) AS int_procs,
        COALESCE(SUM(int_cores_reserved),0) AS int_cores,
+       COALESCE(SUM(int_gpus_reserved),0) AS int_gpus,
        COALESCE(SUM(int_mem_reserved),0) AS int_mem_reserved
     FROM
        job LEFT JOIN proc ON (proc.pk_job = job.pk_job)
@@ -27,8 +226,8 @@ CREATE VIEW vs_job_resource (pk_job, int
        job.pk_job;
 
 
-
-CREATE VIEW vs_alloc_usage (pk_alloc, int_cores, int_idle_cores, int_running_cores, int_locked_cores, int_available_cores, int_hosts, int_locked_hosts, int_down_hosts) AS
+DROP VIEW vs_alloc_usage;
+CREATE VIEW vs_alloc_usage (pk_alloc, int_cores, int_idle_cores, int_running_cores, int_locked_cores, int_available_cores, int_gpus, int_idle_gpus, int_running_gpus, int_locked_gpus, int_available_gpus, int_hosts, int_locked_hosts, int_down_hosts) AS
   SELECT
         alloc.pk_alloc,
         COALESCE(SUM(host.int_cores),0) AS int_cores,
@@ -36,6 +235,11 @@ CREATE VIEW vs_alloc_usage (pk_alloc, in
         COALESCE(SUM(host.int_cores - host.int_cores_idle),0) as int_running_cores,
         COALESCE((SELECT SUM(int_cores) FROM host WHERE host.pk_alloc=alloc.pk_alloc AND (str_lock_state='NIMBY_LOCKED' OR str_lock_state='LOCKED')),0) AS int_locked_cores,
         COALESCE((SELECT SUM(int_cores_idle) FROM host h,host_stat hs WHERE h.pk_host = hs.pk_host AND h.pk_alloc=alloc.pk_alloc AND h.str_lock_state='OPEN' AND hs.str_state ='UP'),0) AS int_available_cores,
+        COALESCE(SUM(host.int_gpus),0) AS int_gpus,
+        COALESCE(SUM(host.int_gpus_idle),0) AS int_idle_gpus,
+        COALESCE(SUM(host.int_gpus - host.int_gpus_idle),0) as int_running_gpus,
+        COALESCE((SELECT SUM(int_gpus) FROM host WHERE host.pk_alloc=alloc.pk_alloc AND (str_lock_state='NIMBY_LOCKED' OR str_lock_state='LOCKED')),0) AS int_locked_gpus,
+        COALESCE((SELECT SUM(int_gpus_idle) FROM host h,host_stat hs WHERE h.pk_host = hs.pk_host AND h.pk_alloc=alloc.pk_alloc AND h.str_lock_state='OPEN' AND hs.str_state ='UP'),0) AS int_available_gpus,
         COUNT(host.pk_host) AS int_hosts,
         (SELECT COUNT(*) FROM host WHERE host.pk_alloc=alloc.pk_alloc AND str_lock_state='LOCKED') AS int_locked_hosts,
         (SELECT COUNT(*) FROM host h,host_stat hs WHERE h.pk_host = hs.pk_host AND h.pk_alloc=alloc.pk_alloc AND hs.str_state='DOWN') AS int_down_hosts
@@ -45,8 +249,8 @@ CREATE VIEW vs_alloc_usage (pk_alloc, in
         alloc.pk_alloc;
 
 
-
-CREATE VIEW vs_folder_counts (pk_folder, int_depend_count, int_waiting_count, int_running_count, int_dead_count, int_cores, int_job_count) AS
+DROP VIEW vs_folder_counts;
+CREATE VIEW vs_folder_counts (pk_folder, int_depend_count, int_waiting_count, int_running_count, int_dead_count, int_cores, int_gpus, int_job_count) AS
   SELECT
     folder.pk_folder,
     COALESCE(SUM(int_depend_count),0) AS int_depend_count,
@@ -54,6 +258,7 @@ CREATE VIEW vs_folder_counts (pk_folder,
     COALESCE(SUM(int_running_count),0) AS int_running_count,
     COALESCE(SUM(int_dead_count),0) AS int_dead_count,
     COALESCE(SUM(int_cores),0) AS int_cores,
+    COALESCE(SUM(int_gpus),0) AS int_gpus,
     COALESCE(COUNT(job.pk_job),0) AS int_job_count
 FROM
     folder
@@ -67,6 +272,7 @@ FROM
       folder.pk_folder;
 
 
+DROP VIEW vs_waiting;
 CREATE VIEW vs_waiting (pk_show) AS
   SELECT
         job.pk_show
@@ -85,13 +291,16 @@ CREATE VIEW vs_waiting (pk_show) AS
     AND
         jr.int_max_cores - jr.int_cores >= 100
     AND
+        jr.int_max_gpus - jr.int_gpus >= 1
+    AND
         job_stat.int_waiting_count != 0
 
     GROUP BY job.pk_show;
 
 
+DROP VIEW v_history_frame;
 CREATE VIEW v_history_frame (pk_frame_history, pk_frame, pk_layer, pk_job, str_name, str_state,
-    int_mem_reserved, int_mem_max_used, int_cores, str_host, int_exit_status, str_alloc_name,
+    int_mem_reserved, int_mem_max_used, int_cores, int_gpus, str_host, int_exit_status, str_alloc_name,
     b_alloc_billable, str_facility_name, int_ts_started, int_ts_stopped, int_checkpoint_count,
     str_show_name, dt_last_modified) AS
   SELECT
@@ -104,6 +313,7 @@ CREATE VIEW v_history_frame (pk_frame_hi
     fh.INT_MEM_RESERVED,
     fh.INT_MEM_MAX_USED,
     fh.INT_CORES,
+    fh.INT_GPUS,
     fh.STR_HOST,
     fh.INT_EXIT_STATUS,
     a.STR_NAME STR_ALLOC_NAME,
@@ -125,8 +335,8 @@ CREATE VIEW v_history_frame (pk_frame_hi
     AND fh.dt_last_modified < (SELECT dt_end FROM history_period);
 
 
-
-CREATE VIEW v_history_job (pk_job, str_name, str_shot, str_user, int_core_time_success, int_core_time_fail, int_frame_count, int_layer_count, int_waiting_count, int_dead_count, int_depend_count, int_eaten_count, int_succeeded_count, int_running_count, int_max_rss, b_archived, str_facility_name, str_dept_name, int_ts_started, int_ts_stopped, str_show_name, dt_last_modified) AS
+DROP VIEW v_history_job;
+CREATE VIEW v_history_job (pk_job, str_name, str_shot, str_user, int_core_time_success, int_core_time_fail, int_gpu_time_success, int_gpu_time_fail, int_frame_count, int_layer_count, int_waiting_count, int_dead_count, int_depend_count, int_eaten_count, int_succeeded_count, int_running_count, int_max_rss, b_archived, str_facility_name, str_dept_name, int_ts_started, int_ts_stopped, str_show_name, dt_last_modified) AS
   select
 jh.PK_JOB,
 jh.STR_NAME,
@@ -134,6 +344,8 @@ jh.STR_SHOT,
 jh.STR_USER,
 jh.INT_CORE_TIME_SUCCESS,
 jh.INT_CORE_TIME_FAIL,
+jh.INT_GPU_TIME_SUCCESS,
+jh.INT_GPU_TIME_FAIL,
 jh.INT_FRAME_COUNT,
 jh.INT_LAYER_COUNT,
 jh.INT_WAITING_COUNT,
@@ -164,9 +376,9 @@ and (
 );
 
 
-
-CREATE VIEW v_history_layer (pk_layer, pk_job, str_name, str_type, int_cores_min,
-    int_mem_min, int_core_time_success, int_core_time_fail, int_frame_count, int_layer_count,
+DROP VIEW v_history_layer;
+CREATE VIEW v_history_layer (pk_layer, pk_job, str_name, str_type, int_cores_min, int_gpus_min,
+    int_mem_min, int_core_time_success, int_core_time_fail, int_gpu_time_success, int_gpu_time_fail, int_frame_count, int_layer_count,
     int_waiting_count, int_dead_count, int_depend_count, int_eaten_count, int_succeeded_count,
     int_running_count, int_max_rss, b_archived, str_services, str_show_name, dt_last_modified) AS
   SELECT
@@ -175,9 +387,12 @@ lh.PK_JOB,
 lh.STR_NAME,
 lh.STR_TYPE,
 lh.INT_CORES_MIN,
+lh.INT_GPUS_MIN,
 lh.INT_MEM_MIN,
 lh.INT_CORE_TIME_SUCCESS,
 lh.INT_CORE_TIME_FAIL,
+lh.INT_GPU_TIME_SUCCESS,
+lh.INT_GPU_TIME_FAIL,
 lh.INT_FRAME_COUNT,
 lh.INT_LAYER_COUNT,
 lh.INT_WAITING_COUNT,
@@ -204,7 +419,18 @@ and jh.dt_last_modified < (
 );
 
 
-CREATE FUNCTION recalculate_subs()
+-- Types
+
+ALTER TYPE JobStatType ADD ATTRIBUTE int_gpu_time_success BIGINT;
+ALTER TYPE JobStatType ADD ATTRIBUTE int_gpu_time_fail BIGINT;
+
+ALTER TYPE LayerStatType ADD ATTRIBUTE int_gpu_time_success BIGINT;
+ALTER TYPE LayerStatType ADD ATTRIBUTE int_gpu_time_fail BIGINT;
+
+
+-- Functions
+
+CREATE OR REPLACE FUNCTION recalculate_subs()
 RETURNS VOID AS $body$
 DECLARE
     r RECORD;
@@ -213,13 +439,14 @@ BEGIN
   -- concatenates all tags in host_tag and sets host.str_tags
   --
   UPDATE subscription SET int_cores = 0;
+  UPDATE subscription SET int_gpus = 0;
   FOR r IN
-    SELECT proc.pk_show, alloc.pk_alloc, sum(proc.int_cores_reserved) as c
+    SELECT proc.pk_show, alloc.pk_alloc, sum(proc.int_cores_reserved) as c, proc.pk_show, alloc.pk_alloc, sum(proc.int_gpus_reserved) as d
     FROM proc, host, alloc
     WHERE proc.pk_host = host.pk_host AND host.pk_alloc = alloc.pk_alloc
     GROUP BY proc.pk_show, alloc.pk_alloc
   LOOP
-    UPDATE subscription SET int_cores = r.c WHERE pk_alloc=r.pk_alloc AND pk_show=r.pk_show;
+    UPDATE subscription SET int_cores = r.c, int_gpus = r.d WHERE pk_alloc=r.pk_alloc AND pk_show=r.pk_show;
 
   END LOOP;
 END;
@@ -227,18 +454,18 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION tmp_populate_folder()
+CREATE OR REPLACE FUNCTION tmp_populate_folder()
 RETURNS VOID AS $body$
 DECLARE
     t RECORD;
 BEGIN
     FOR t IN
-        SELECT pk_folder, pk_show, sum(int_cores) AS c
+        SELECT pk_folder, pk_show, sum(int_cores) AS c, pk_folder, pk_show, sum(int_gpus) AS d
         FROM job, job_resource
         WHERE job.pk_job = job_resource.pk_job
         GROUP by pk_folder, pk_show
     LOOP
-        UPDATE folder_resource SET int_cores = t.c WHERE pk_folder = t.pk_folder;
+        UPDATE folder_resource SET int_cores = t.c, int_gpus = t.d WHERE pk_folder = t.pk_folder;
         COMMIT;
     END LOOP;
 END;
@@ -246,49 +473,50 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION tmp_populate_point()
+CREATE OR REPLACE FUNCTION tmp_populate_point()
 RETURNS VOID AS $body$
 DECLARE
     t RECORD;
 BEGIN
     FOR t IN
-        SELECT pk_dept, pk_show, sum(int_cores) AS c
+        SELECT pk_dept, pk_show, sum(int_cores) AS c, sum(int_gpus) AS d
         FROM job, job_resource
         WHERE job.pk_job = job_resource.pk_job
         GROUP BY pk_dept, pk_show
     LOOP
-        UPDATE point SET int_cores = t.c WHERE pk_show = t.pk_show AND pk_dept = t.pk_dept;
+        UPDATE point SET int_cores = t.c , int_gpus = t.d WHERE pk_show = t.pk_show AND pk_dept = t.pk_dept;
     END LOOP;
 END;
 $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION tmp_populate_sub()
+CREATE OR REPLACE FUNCTION tmp_populate_sub()
 RETURNS VOID AS $body$
 DECLARE
     t RECORD;
 BEGIN
     FOR t IN
-        SELECT proc.pk_show, host.pk_alloc, sum(int_cores_reserved) AS c
+        SELECT proc.pk_show, host.pk_alloc, sum(int_cores_reserved) AS c, proc.pk_show, host.pk_alloc, sum(int_gpus_reserved) AS d
         FROM proc, host
         WHERE proc.pk_host = host.pk_host
         GROUP BY proc.pk_show, host.pk_alloc
     LOOP
-        UPDATE subscription SET int_cores = t.c WHERE pk_show = t.pk_show AND pk_alloc = t.pk_alloc;
+        UPDATE subscription SET int_cores = t.c, int_gpus = t.d WHERE pk_show = t.pk_show AND pk_alloc = t.pk_alloc;
     END LOOP;
 END;
 $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__after_job_moved()
+CREATE OR REPLACE FUNCTION trigger__after_job_moved()
 RETURNS TRIGGER AS $body$
 DECLARE
     int_core_count INT;
+    int_gpu_count INT;
 BEGIN
-    SELECT int_cores INTO int_core_count
-    FROM job_resource WHERE pk_job = NEW.pk_job;
+    SELECT int_cores INTO int_core_count FROM job_resource WHERE pk_job = NEW.pk_job;
+    SELECT int_gpus INTO int_gpu_count FROM job_resource WHERE pk_job = NEW.pk_job;
 
     IF int_core_count > 0 THEN
         UPDATE folder_resource SET int_cores = int_cores + int_core_count
@@ -297,13 +525,21 @@ BEGIN
         UPDATE folder_resource  SET int_cores = int_cores - int_core_count
         WHERE pk_folder = OLD.pk_folder;
     END IF;
+
+    IF int_gpu_count > 0 THEN
+        UPDATE folder_resource SET int_gpus = int_gpus + int_gpu_count
+        WHERE pk_folder = NEW.pk_folder;
+
+        UPDATE folder_resource  SET int_gpus = int_gpus - int_gpu_count
+        WHERE pk_folder = OLD.pk_folder;
+    END IF;
     RETURN NULL;
 END
 $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__before_delete_job()
+CREATE OR REPLACE FUNCTION trigger__before_delete_job()
 RETURNS TRIGGER AS $body$
 DECLARE
     js JobStatType;
@@ -311,6 +547,8 @@ BEGIN
     SELECT
         job_usage.int_core_time_success,
         job_usage.int_core_time_fail,
+        job_usage.int_gpu_time_success,
+        job_usage.int_gpu_time_fail,
         job_stat.int_waiting_count,
         job_stat.int_dead_count,
         job_stat.int_depend_count,
@@ -337,6 +575,8 @@ BEGIN
         pk_dept = OLD.pk_dept,
         int_core_time_success = js.int_core_time_success,
         int_core_time_fail = js.int_core_time_fail,
+        int_gpu_time_success = js.int_gpu_time_success,
+        int_gpu_time_fail = js.int_gpu_time_fail,
         int_frame_count = OLD.int_frame_count,
         int_layer_count = OLD.int_layer_count,
         int_waiting_count = js.int_waiting_count,
@@ -367,7 +607,7 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__after_job_finished()
+CREATE OR REPLACE FUNCTION trigger__after_job_finished()
 RETURNS TRIGGER AS $body$
 DECLARE
     ts INT := cast(epoch(current_timestamp) as integer);
@@ -378,6 +618,8 @@ BEGIN
     SELECT
         job_usage.int_core_time_success,
         job_usage.int_core_time_fail,
+        job_usage.int_gpu_time_success,
+        job_usage.int_gpu_time_fail,
         job_stat.int_waiting_count,
         job_stat.int_dead_count,
         job_stat.int_depend_count,
@@ -404,6 +646,8 @@ BEGIN
         pk_dept = NEW.pk_dept,
         int_core_time_success = js.int_core_time_success,
         int_core_time_fail = js.int_core_time_fail,
+        int_gpu_time_success = js.int_gpu_time_success,
+        int_gpu_time_fail = js.int_gpu_time_fail,
         int_frame_count = NEW.int_frame_count,
         int_layer_count = NEW.int_layer_count,
         int_waiting_count = js.int_waiting_count,
@@ -422,6 +666,8 @@ BEGIN
         SELECT
             layer_usage.int_core_time_success,
             layer_usage.int_core_time_fail,
+            layer_usage.int_gpu_time_success,
+            layer_usage.int_gpu_time_fail,
             layer_stat.int_total_count,
             layer_stat.int_waiting_count,
             layer_stat.int_dead_count,
@@ -448,6 +694,8 @@ BEGIN
         SET
             int_core_time_success = ls.int_core_time_success,
             int_core_time_fail = ls.int_core_time_fail,
+            int_gpu_time_success = ls.int_gpu_time_success,
+            int_gpu_time_fail = ls.int_gpu_time_fail,
             int_frame_count = ls.int_total_count,
             int_waiting_count = ls.int_waiting_count,
             int_dead_count = ls.int_dead_count,
@@ -471,15 +719,17 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__after_job_dept_update()
+CREATE OR REPLACE FUNCTION trigger__after_job_dept_update()
 RETURNS TRIGGER AS $body$
 DECLARE
     int_running_cores INT;
+    int_running_gpus INT;
 BEGIN
   /**
   * Handles the accounting for moving a job between departments.
   **/
   SELECT int_cores INTO int_running_cores FROM job_resource WHERE pk_job = NEW.pk_job;
+  SELECT int_gpus INTO int_running_gpus FROM job_resource WHERE pk_job = NEW.pk_job;
 
   IF int_running_cores > 0 THEN
     UPDATE point SET int_cores = int_cores + int_running_cores
@@ -489,13 +739,21 @@ BEGIN
         WHERE pk_dept = OLD.pk_dept AND pk_show = OLD.pk_show;
   END IF;
 
+  IF int_running_gpus > 0 THEN
+    UPDATE point SET int_gpus = int_gpus + int_running_gpus
+        WHERE pk_dept = NEW.pk_dept AND pk_show = NEW.pk_show;
+
+    UPDATE point SET int_gpus = int_gpus - int_running_gpus
+        WHERE pk_dept = OLD.pk_dept AND pk_show = OLD.pk_show;
+  END IF;
+
   RETURN NULL;
 END;
 $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__verify_host_local()
+CREATE OR REPLACE FUNCTION trigger__verify_host_local()
 RETURNS TRIGGER AS $body$
 BEGIN
     /**
@@ -508,6 +766,10 @@ BEGIN
         RAISE EXCEPTION 'host local doesnt have enough idle cores.';
     END IF;
 
+    IF NEW.int_gpus_idle < 0 THEN
+        RAISE EXCEPTION 'host local doesnt have enough GPU idle cores.';
+    END IF;
+
     IF NEW.int_mem_idle < 0 THEN
         RAISE EXCEPTION 'host local doesnt have enough idle memory';
     END IF;
@@ -517,10 +779,13 @@ END;
 $body$
 LANGUAGE PLPGSQL;
 
+DROP TRIGGER verify_host_local ON host_local;
 CREATE TRIGGER verify_host_local BEFORE UPDATE ON host_local
 FOR EACH ROW
    WHEN ((NEW.int_cores_max = OLD.int_cores_max AND NEW.int_mem_max = OLD.int_mem_max) AND
-    (NEW.int_cores_idle != OLD.int_cores_idle OR NEW.int_mem_idle != OLD.int_mem_idle))
+    (NEW.int_cores_idle != OLD.int_cores_idle OR NEW.int_mem_idle != OLD.int_mem_idle) AND
+    (NEW.int_gpus_max = OLD.int_gpus_max AND NEW.int_mem_max = OLD.int_mem_max) AND
+    (NEW.int_gpus_idle != OLD.int_gpus_idle OR NEW.int_mem_idle != OLD.int_mem_idle))
    EXECUTE PROCEDURE trigger__verify_host_local();
 
 
@@ -533,9 +798,9 @@ BEGIN
     INSERT INTO layer_mem (pk_layer_mem, pk_layer, pk_job) VALUES (NEW.pk_layer, NEW.pk_layer, NEW.pk_job);
 
     INSERT INTO layer_history
-        (pk_layer, pk_job, str_name, str_type, int_cores_min, int_mem_min, b_archived,str_services)
+        (pk_layer, pk_job, str_name, str_type, int_cores_min, int_gpus_min, int_mem_min, b_archived,str_services)
     VALUES
-        (NEW.pk_layer, NEW.pk_job, NEW.str_name, NEW.str_type, NEW.int_cores_min, NEW.int_mem_min, false, NEW.str_services);
+        (NEW.pk_layer, NEW.pk_job, NEW.str_name, NEW.str_type, NEW.int_cores_min, NEW.int_gpus_min, NEW.int_mem_min, false, NEW.str_services);
 
     RETURN NEW;
 END;
@@ -543,7 +808,7 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__before_delete_layer()
+CREATE OR REPLACE FUNCTION trigger__before_delete_layer()
 RETURNS TRIGGER AS $body$
 DECLARE
     js LayerStatType;
@@ -551,6 +816,8 @@ BEGIN
     SELECT
         layer_usage.int_core_time_success,
         layer_usage.int_core_time_fail,
+        layer_usage.int_gpu_time_success,
+        layer_usage.int_gpu_time_fail,
         layer_stat.int_total_count,
         layer_stat.int_waiting_count,
         layer_stat.int_dead_count,
@@ -577,6 +844,8 @@ BEGIN
     SET
         int_core_time_success = js.int_core_time_success,
         int_core_time_fail = js.int_core_time_fail,
+        int_gpu_time_success = js.int_gpu_time_success,
+        int_gpu_time_fail = js.int_gpu_time_fail,
         int_frame_count = js.int_total_count,
         int_waiting_count = js.int_waiting_count,
         int_dead_count = js.int_dead_count,
@@ -602,7 +871,7 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__verify_host_resources()
+CREATE OR REPLACE FUNCTION trigger__verify_host_resources()
 RETURNS TRIGGER AS $body$
 BEGIN
     IF NEW.int_cores_idle < 0 THEN
@@ -613,23 +882,29 @@ BEGIN
         RAISE EXCEPTION 'unable to allocate additional memory';
     END IF;
 
-    If NEW.int_gpu_idle < 0 THEN
-        RAISE EXCEPTION 'unable to allocate additional gpu memory';
+    If NEW.int_gpus_idle < 0 THEN
+        RAISE EXCEPTION 'unable to allocate additional GPU units';
     END IF;
 
+    If NEW.int_gpu_mem_idle < 0 THEN
+        RAISE EXCEPTION 'unable to allocate additional GPU memory';
+    END IF;
     RETURN NEW;
 END;
 $body$
 LANGUAGE PLPGSQL;
 
+DROP TRIGGER verify_host_resources ON host;
 CREATE TRIGGER verify_host_resources BEFORE UPDATE ON host
 FOR EACH ROW
    WHEN (NEW.int_cores_idle != OLD.int_cores_idle
-        OR NEW.int_mem_idle != OLD.int_mem_idle)
+        OR NEW.int_mem_idle != OLD.int_mem_idle
+        OR NEW.int_gpus_idle != OLD.int_gpus_idle
+        OR NEW.int_gpu_mem_idle != OLD.int_gpu_mem_idle)
    EXECUTE PROCEDURE trigger__verify_host_resources();
 
 
-CREATE FUNCTION trigger__verify_job_resources()
+CREATE OR REPLACE FUNCTION trigger__verify_job_resources()
 RETURNS TRIGGER AS $body$
 BEGIN
     /**
@@ -641,18 +916,23 @@ BEGIN
     IF NEW.int_cores > NEW.int_max_cores THEN
         RAISE EXCEPTION 'job has exceeded max cores';
     END IF;
+    IF NEW.int_gpus > NEW.int_max_gpus THEN
+        RAISE EXCEPTION 'job has exceeded max GPU units';
+    END IF;
     RETURN NEW;
 END;
 $body$
 LANGUAGE PLPGSQL;
 
+DROP TRIGGER verify_job_resources ON job_resource;
 CREATE TRIGGER verify_job_resources BEFORE UPDATE ON job_resource
 FOR EACH ROW
-  WHEN (NEW.int_max_cores = OLD.int_max_cores AND NEW.int_cores > OLD.int_cores)
+  WHEN (NEW.int_max_cores = OLD.int_max_cores AND NEW.int_cores > OLD.int_cores OR
+        NEW.int_max_gpus = OLD.int_max_gpus AND NEW.int_gpus > OLD.int_gpus)
   EXECUTE PROCEDURE trigger__verify_job_resources();
 
 
-CREATE FUNCTION trigger__update_proc_update_layer()
+CREATE OR REPLACE FUNCTION trigger__update_proc_update_layer()
 RETURNS TRIGGER AS $body$
 DECLARE
     lr RECORD;
@@ -670,14 +950,16 @@ BEGIN
       IF lr.pk_layer = OLD.pk_layer THEN
 
         UPDATE layer_resource SET
-          int_cores = int_cores - OLD.int_cores_reserved
+          int_cores = int_cores - OLD.int_cores_reserved,
+          int_gpus = int_gpus - OLD.int_gpus_reserved
         WHERE
           pk_layer = OLD.pk_layer;
 
       ELSE
 
         UPDATE layer_resource SET
-          int_cores = int_cores + NEW.int_cores_reserved
+          int_cores = int_cores + NEW.int_cores_reserved,
+          int_gpus = int_gpus + NEW.int_gpus_reserved
        WHERE
           pk_layer = NEW.pk_layer;
        END IF;
@@ -689,7 +971,7 @@ $body$
 LANGUAGE PLPGSQL;
 
 
-CREATE FUNCTION trigger__frame_history_open()
+CREATE OR REPLACE FUNCTION trigger__frame_history_open()
 RETURNS TRIGGER AS $body$
 DECLARE
   str_pk_alloc VARCHAR(36) := null;
@@ -741,19 +1023,21 @@ BEGIN
             str_name,
             str_state,
             int_cores,
+            int_gpus,
             int_mem_reserved,
             str_host,
             int_ts_started,
             pk_alloc
          )
          VALUES
-            ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)'
+            ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)'
          USING NEW.pk_frame,
             NEW.pk_layer,
             NEW.pk_job,
             NEW.str_name,
             'RUNNING',
             NEW.int_cores,
+            NEW.int_gpus,
             NEW.int_mem_reserved,
             NEW.str_host,
             epoch(current_timestamp),