--- a/schema/gemma.sql	Wed Jul 24 13:29:31 2019 +0200
+++ b/schema/gemma.sql	Wed Jul 24 16:15:21 2019 +0200
@@ -151,45 +151,55 @@
 $$
 LANGUAGE plpgsql;
 
--- REMOVE ME:
-CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
+-- Trigger function to be used as a constraint trigger to enforce
+-- existance of a referenced bottleneck with validity at a given time.
+-- The columns with the referenced bottleneck id and the timestamp are
+-- given as arguments to the trigger function.
+CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
 $$
+DECLARE
+    referenced_bottleneck_id text;
+    new_tstz timestamptz;
 BEGIN
-    -- Avoid unnecessary execution ON UPDATE if validity did not change
-    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
-        UPDATE waterway.sounding_results
-        SET bottleneck_validity = NEW.validity
-        WHERE bottleneck_id = NEW.bottleneck_id
-          AND CAST(date_info AS timestamptz) <@ NEW.validity;
-
-        -- Always associate fairway availability data to newest bottleneck
-        -- version to prevent problems in analysis over longer time periods
-        WITH
-        bn AS (SELECT id, validity FROM waterway.bottlenecks
-            WHERE bottleneck_id = NEW.bottleneck_id),
-        latest AS (SELECT id FROM bn
-            -- Candidates are past new validity or just inserted/updated
-            WHERE NOT validity &< NEW.validity OR id = NEW.id
-            ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY)
-        UPDATE waterway.fairway_availability
-        SET bottleneck_id = (SELECT id FROM latest)
-        WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest);
+    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
+        INTO referenced_bottleneck_id
+        USING NEW;
+    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
+        INTO new_tstz
+        USING NEW;
+    IF EXISTS ( SELECT * FROM waterway.bottlenecks
+                  WHERE bottleneck_id = referenced_bottleneck_id
+                  AND validity @> new_tstz )
+    THEN
+        RETURN NEW;
+    ELSE
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = format('No matching bottleneck %s for %s found.',
+                    referenced_bottleneck_id, new_tstz),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
     END IF;
-    RETURN NULL; -- ignored
 END;
 $$
 LANGUAGE plpgsql;
 
 -- Constraint trigger: sounding Results must intersect with the area
--- of the bottleneck they belong to.
+-- of the bottleneck they belong to.  The "xx" at the beginning of the
+-- name is to ensure, it is fired last after other triggers.
 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
 LANGUAGE plpgsql
 AS $$
 BEGIN
     IF NOT st_intersects((SELECT area
                           FROM waterway.bottlenecks
-                          WHERE (bottleneck_id, validity)
-                              =(NEW.bottleneck_id, NEW.bottleneck_validity)),
+                          WHERE bottleneck_id = NEW.bottleneck_id
+                            AND validity @> NEW.date_info::timestamptz),
                          NEW.area)
     THEN
         RAISE EXCEPTION
@@ -668,10 +678,6 @@
     CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
         AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
         FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
-    -- Associate referencing objects to matching bottleneck version
-    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
-        ON bottlenecks FOR EACH ROW
-        EXECUTE FUNCTION move_bottleneck_referencing()
 
     CREATE TABLE bottlenecks_riverbed_materials (
         bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
@@ -684,14 +690,7 @@
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
-        bottleneck_validity tstzrange NOT NULL,
-        CONSTRAINT bottleneck_key
-            FOREIGN KEY (bottleneck_id, bottleneck_validity)
-                REFERENCES bottlenecks (bottleneck_id, validity)
-                ON UPDATE CASCADE,
         date_info date NOT NULL,
-        CHECK (tstzrange(date_info::timestamptz,
-            date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
         UNIQUE (bottleneck_id, date_info),
         area geography(POLYGON, 4326) NOT NULL
             CHECK(ST_IsValid(CAST(area AS geometry))),
@@ -702,9 +701,13 @@
         octree_index bytea,
         staging_done boolean NOT NULL DEFAULT false
     )
-    CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area
-        AFTER INSERT OR UPDATE ON sounding_results
-        FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area()
+    CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
+        AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
+        FOR EACH ROW
+        EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
+    CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
+        AFTER INSERT OR UPDATE ON waterway.sounding_results
+        FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
 
     CREATE TABLE sounding_results_contour_lines (
         sounding_result_id int NOT NULL REFERENCES sounding_results