--- a/schema/gemma.sql	Thu Aug 01 12:48:33 2019 +0200
+++ b/schema/gemma.sql	Fri Aug 02 13:30:29 2019 +0200
@@ -103,7 +103,7 @@
             USING
                 DETAIL = format('No matching gauge %s found.',
                     isrs_AsText(referenced_gauge::isrs)),
-                ERRCODE = 23505,
+                ERRCODE = 23503,
                 SCHEMA = TG_TABLE_SCHEMA,
                 TABLE = TG_TABLE_NAME,
                 COLUMN = TG_ARGV[0],
@@ -141,7 +141,7 @@
             USING
                 DETAIL = format('No matching gauge %s found.',
                     isrs_AsText(referenced_gauge::isrs)),
-                ERRCODE = 23505,
+                ERRCODE = 23503,
                 SCHEMA = TG_TABLE_SCHEMA,
                 TABLE = TG_TABLE_NAME,
                 COLUMN = TG_ARGV[0],
@@ -179,7 +179,7 @@
             USING
                 DETAIL = format('No matching bottleneck %s for %s found.',
                     referenced_bottleneck_id, new_tstz),
-                ERRCODE = 23505,
+                ERRCODE = 23503,
                 SCHEMA = TG_TABLE_SCHEMA,
                 TABLE = TG_TABLE_NAME,
                 COLUMN = TG_ARGV[0],
@@ -649,9 +649,6 @@
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
         validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
-        UNIQUE (bottleneck_id, validity),
-        EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
-            DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
         objnam varchar,
         nobjnm varchar,
@@ -673,7 +670,12 @@
         -- XXX: Also an attribut of sounding result?
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
-        staging_done boolean NOT NULL DEFAULT false
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE (bottleneck_id, validity, staging_done),
+        EXCLUDE USING GiST (bottleneck_id WITH =,
+                            validity WITH &&,
+                            CAST(staging_done AS int) WITH =)
+            DEFERRABLE INITIALLY DEFERRED
     )
     CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
         AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
@@ -881,9 +883,13 @@
     tmp RECORD;
 BEGIN
     FOR tmp IN
-        SELECT * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion
+        SELECT relation, array_agg(key) AS keys
+            FROM import.track_imports
+            WHERE import_id = imp_id AND NOT deletion
+            GROUP BY relation
     LOOP
-        EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
+        EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation)
+            USING tmp.keys;
     END LOOP;
 END;
 $$