Mercurial > gemma
changeset 4132:ec8438712447
Enable better error handling for referenced gauges/bottlenecks constraints.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Thu, 01 Aug 2019 19:12:47 +0200 |
| parents | 49ec33a7f954 |
| children | eb08fbe33074 |
| files | pkg/pgxutils/errors.go schema/gemma.sql schema/updates/1103/01.fix_constraint_error_codes.sql schema/version.sql |
| diffstat | 4 files changed, 133 insertions(+), 5 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/pgxutils/errors.go Thu Aug 01 18:53:51 2019 +0200 +++ b/pkg/pgxutils/errors.go Thu Aug 01 19:12:47 2019 +0200 @@ -79,7 +79,7 @@ switch err.TableName { case "gauge_measurements", "gauge_predictions", "bottlenecks": switch err.ConstraintName { - case "gauge_key": + case "gauge_key", "waterway_bottlenecks_reference_gauge": m = "Referenced gauge with matching temporal validity not available" return }
--- a/schema/gemma.sql Thu Aug 01 18:53:51 2019 +0200 +++ b/schema/gemma.sql Thu Aug 01 19:12:47 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],
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1103/01.fix_constraint_error_codes.sql Thu Aug 01 19:12:47 2019 +0200 @@ -0,0 +1,128 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <sascha.wilde@intevation.de> + +-- +-- CONSTRAINT FUNCTIONS +-- +-- The only change in the following functions is the error code +-- returend in case of failure: it should be +-- 23503: foreign_key_violation +-- as what we are emulating is kind of an foreign key... + +CREATE OR REPLACE FUNCTION check_valid_gauge() 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_validity tstzrange; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_validity + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + AND validity && new_validity ) + 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 = 23503, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + +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 = 23503, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS +$$ +DECLARE + referenced_bottleneck_id text; + new_tstz timestamptz; +BEGIN + 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 = 23503, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql;