Mercurial > gemma
changeset 4045:12e3933b2050 historization_ng
Added trigger constraints to ensure matching gauges for gauge measurements.
1. Remind me to never write "Finished" in commit messages... ;-)
2. This might be bad for performance, and might be of limited use
anyway, so the triggers might be changed or removed later...
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 24 Jul 2019 13:29:31 +0200 |
parents | f42f7f7eb81f |
children | 8c6bc85db711 |
files | schema/gemma.sql schema/updates/1100/01.remove_gauge_validity_refs.sql |
diffstat | 2 files changed, 94 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jul 24 13:00:37 2019 +0200 +++ b/schema/gemma.sql Wed Jul 24 13:29:31 2019 +0200 @@ -113,6 +113,44 @@ $$ LANGUAGE plpgsql; +-- The same for objects with a timestamp instead of a validity range. +CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS +$$ +DECLARE + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + 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 gauge %s found.', + isrs_AsText(referenced_gauge::isrs)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + -- REMOVE ME: CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS $$ @@ -435,6 +473,9 @@ staging_done boolean NOT NULL DEFAULT false, UNIQUE (measure_date, location, staging_done) ) + CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); -- For fast retrieval of newest measurement per location: CREATE INDEX gauge_measurements_location_measure_date_desc ON waterway.gauge_measurements (location, measure_date DESC) @@ -454,6 +495,9 @@ source_organization varchar NOT NULL, -- "originator" from NtS response PRIMARY KEY (measure_date, location, date_issue) ) + CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
--- a/schema/updates/1100/01.remove_gauge_validity_refs.sql Wed Jul 24 13:00:37 2019 +0200 +++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql Wed Jul 24 13:29:31 2019 +0200 @@ -55,6 +55,44 @@ $$ LANGUAGE plpgsql; +-- The same for objects with a timestamp instead of a validity range. +CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS +$$ +DECLARE + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + 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 gauge %s for %s found.', + (isrs_AsText(referenced_gauge::isrs)), new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + -- -- BOTTLENECKS @@ -125,8 +163,20 @@ -- As we resolve the correct gauge data to use on runtime, we drop the -- hard reference to the gauges vaidity: ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity; + +DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge + ON waterway.gauge_measurements; +CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); + ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity; +DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge + ON waterway.gauge_predictions; +CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); -- -- DROP NO LONGER NEEDED TRIGGER FUNCTION