Mercurial > gemma
changeset 5141:722b7c305319
Prevent equal axis geometries with intersecting validity
Such things usually cannot happen via importing, but it is at least
consistent with constraints on other tables and the functionality
is needed for fairway dimensions, since they are first stored in the
staging area and equal geometries with intersecting validity could
be generated by a concurrent import.
Set the CONSTRAINT TRIGGER as INITIALLY DEFERRED because new entries
are inserted before the validity of existing entries is adapted
in the same transaction.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 31 Mar 2020 12:50:30 +0200 |
| parents | 11c4c5b8041c |
| children | ea22d3efa76c |
| files | schema/gemma.sql schema/gemma_tests.sql schema/run_tests.sh schema/updates/1436/01.amend_prevent_st_equals.sql schema/updates/1436/02.axis_no_intersecting_validity.sql schema/version.sql |
| diffstat | 6 files changed, 82 insertions(+), 6 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Tue Mar 31 10:52:26 2020 +0200 +++ b/schema/gemma.sql Tue Mar 31 12:50:30 2020 +0200 @@ -71,13 +71,17 @@ -- Trigger function to be used as a constraint trigger to enforce uniqueness -- of geometries in the column with its name given as an argument to the --- trigger function. If additional column names are given, --- the group of given columns is tested for equality. +-- trigger function. If additional column names are given as trigger arguments, +-- the group of given columns is tested for equality, if no operators are +-- given. Optionally, a column can be compared with a different operator +-- given after the keyword "WITH" in the trigger argument. CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; tg_arg text; + col varchar; + opr varchar; filters text; has_equal boolean; BEGIN @@ -86,7 +90,11 @@ USING NEW; FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP -- Test each additional argument for equality - filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg); + -- or with operator given after keyword "WITH" + tg_arg = lower(tg_arg); + col = split_part(tg_arg, ' with ', 1); + opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); + filters = format('%s AND %I %s $2.%2$I', filters, col, opr); END LOOP; EXECUTE format( 'SELECT EXISTS(SELECT 1 FROM %I.%I ' @@ -632,7 +640,9 @@ ) CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis - FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity') + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&') CREATE INDEX waterway_axis_validity ON waterway_axis USING GiST (validity)
--- a/schema/gemma_tests.sql Tue Mar 31 10:52:26 2020 +0200 +++ b/schema/gemma_tests.sql Tue Mar 31 12:50:30 2020 +0200 @@ -25,6 +25,7 @@ 'NULL value is not checked'); SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'), (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test') @@ -32,6 +33,21 @@ 23505, NULL, 'No duplicate geometries can be inserted into waterway_axis'); +SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; + INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(NULL, current_timestamp) + ), ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(current_timestamp - interval '1d', NULL) + ) + $$, + 23505, NULL, + 'Duplicate axis geometries cannot be inserted if validities intersect'); + SELECT lives_ok($$ INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
--- a/schema/run_tests.sh Tue Mar 31 10:52:26 2020 +0200 +++ b/schema/run_tests.sh Tue Mar 31 12:50:30 2020 +0200 @@ -80,7 +80,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f "$BASEDIR"/tap_tests_data.sql \ - -c "SELECT plan(88 + ( + -c "SELECT plan(89 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1436/01.amend_prevent_st_equals.sql Tue Mar 31 12:50:30 2020 +0200 @@ -0,0 +1,43 @@ +CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS +$$ +DECLARE + new_geom geometry; + tg_arg text; + col varchar; + opr varchar; + filters text; + has_equal boolean; +BEGIN + EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) + INTO new_geom + USING NEW; + FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP + tg_arg = lower(tg_arg); + col = split_part(tg_arg, ' with ', 1); + opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); + filters = format('%s AND %I %s $2.%2$I', filters, col, opr); + END LOOP; + EXECUTE format( + 'SELECT EXISTS(SELECT 1 FROM %I.%I ' + 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))' + '%s)', + TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters) + INTO has_equal + USING new_geom, NEW; + IF has_equal THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('Failing row contains geometry in %s', + Box2D(new_geom)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$ +LANGUAGE plpgsql;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1436/02.axis_no_intersecting_validity.sql Tue Mar 31 12:50:30 2020 +0200 @@ -0,0 +1,7 @@ +DROP TRIGGER waterway_axis_wtwaxs_unique ON waterway.waterway_axis; + +CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique + AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway.waterway_axis + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&');