# HG changeset patch # User Tom Gottfried # Date 1560583468 -7200 # Node ID 29ef6d41e4af2904e5918b809ce522b8d40eb5da # Parent 58508f50d1924d9ff8eb2b9501a9ea65475b8397 Use database triggers to move referencing objects to new versions Needs fewer database round-trips and is more convenient especially if more than two levels in the object hierarchy have to be handled. diff -r 58508f50d192 -r 29ef6d41e4af pkg/imports/bn.go --- a/pkg/imports/bn.go Fri Jun 14 17:56:42 2019 +0200 +++ b/pkg/imports/bn.go Sat Jun 15 09:24:28 2019 +0200 @@ -107,14 +107,6 @@ ) RETURNING id` - moveSRSQL = ` -UPDATE waterway.sounding_results --- Associate measurements to matching bottleneck version -SET bottleneck_validity = $2 -WHERE bottleneck_id = $1 - AND CAST(date_info AS timestamptz) <@ CAST($2 AS tstzrange) -` - fixBNValiditySQL = ` UPDATE waterway.bottlenecks SET -- Set enddate of old entry to new startdate in case of overlap: @@ -273,7 +265,7 @@ feedback.Info("Found %d bottlenecks for import", len(bns)) - var hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + var hasStmt, insertStmt, fixValidityStmt, updateStmt, deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt for _, x := range []struct { @@ -282,7 +274,6 @@ }{ {hasBottleneckSQL, &hasStmt}, {insertBottleneckSQL, &insertStmt}, - {moveSRSQL, &moveSRStmt}, {fixBNValiditySQL, &fixValidityStmt}, {updateBottleneckSQL, &updateStmt}, {deleteBottleneckMaterialSQL, &deleteMaterialStmt}, @@ -303,7 +294,7 @@ for _, bn := range bns { if err := storeBottleneck( ctx, importID, conn, feedback, bn, &nids, tolerance, - hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + hasStmt, insertStmt, fixValidityStmt, updateStmt, deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil { return nil, err } @@ -330,7 +321,7 @@ bn *ifbn.BottleNeckType, nids *[]string, tolerance float64, - hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, + hasStmt, insertStmt, fixValidityStmt, updateStmt, deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt, ) error { feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id) @@ -512,18 +503,6 @@ } } - // Move sounding results to new matching bottleneck version, if applicable - if _, err = tx.StmtContext(ctx, moveSRStmt).ExecContext(ctx, - bn.Bottleneck_id, - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return err2 - } - return nil - } - // Set end of validity of old version to start of new version // in case of overlap if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx, diff -r 58508f50d192 -r 29ef6d41e4af pkg/imports/wg.go --- a/pkg/imports/wg.go Fri Jun 14 17:56:42 2019 +0200 +++ b/pkg/imports/wg.go Sat Jun 15 09:24:28 2019 +0200 @@ -116,22 +116,6 @@ ) ` - moveGMSQL = ` -UPDATE waterway.gauge_measurements --- Associate measurements to matching gauge version -SET validity = $2 -WHERE isrs_astext(location) = $1 - AND measure_date <@ CAST($2 AS tstzrange) -` - - moveBNSQL = ` -UPDATE waterway.bottlenecks --- Associate bottlenecks to matching gauge version -SET gauge_validity = $2 -WHERE isrs_astext(gauge_location) = $1 - AND lower(validity) <@ CAST($2 AS tstzrange) -` - fixValiditySQL = ` UPDATE waterway.gauges SET -- Set enddate of old entry to new startdate in case of overlap: @@ -208,7 +192,7 @@ return nil, err } - var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt, + var eraseGaugeStmt, insertStmt, fixValidityStmt, updateStmt, deleteReferenceWaterLevelsStmt, isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt @@ -218,8 +202,6 @@ }{ {eraseGaugeSQL, &eraseGaugeStmt}, {insertGaugeSQL, &insertStmt}, - {moveGMSQL, &moveGMStmt}, - {moveBNSQL, &moveBNStmt}, {fixValiditySQL, &fixValidityStmt}, {updateGaugeSQL, &updateStmt}, {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt}, @@ -446,31 +428,6 @@ } } - // Move gauge measurements and bottlenecks to new matching - // gauge version, if applicable - if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx, - code.String(), - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return nil, err2 - } - unchanged++ - continue - } - if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx, - code.String(), - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return nil, err2 - } - unchanged++ - continue - } - // Set end of validity of old version to start of new version // in case of overlap if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext( diff -r 58508f50d192 -r 29ef6d41e4af schema/auth_tests.sql --- a/schema/auth_tests.sql Fri Jun 14 17:56:42 2019 +0200 +++ b/schema/auth_tests.sql Sat Jun 15 09:24:28 2019 +0200 @@ -86,7 +86,8 @@ $2, 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization' FROM waterway.gauges - WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs; + WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs + AND NOT erased; SELECT lives_ok($$ EXECUTE bn_insert( 'test1', diff -r 58508f50d192 -r 29ef6d41e4af schema/gemma.sql --- a/schema/gemma.sql Fri Jun 14 17:56:42 2019 +0200 +++ b/schema/gemma.sql Sat Jun 15 09:24:28 2019 +0200 @@ -72,6 +72,43 @@ $$ LANGUAGE plpgsql; +-- Trigger functions to be used as statement-level AFTER triggers, +-- associating time-based referencing objects to matching version +CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS +$$ +BEGIN + -- Avoid unnecessary execution ON UPDATE if validity did not change + IF OLD IS NULL OR NEW.validity <> OLD.validity THEN + UPDATE waterway.gauge_measurements + SET validity = NEW.validity + WHERE location = NEW.location + AND measure_date <@ NEW.validity; + + UPDATE waterway.bottlenecks + SET gauge_validity = NEW.validity + WHERE gauge_location = NEW.location + AND lower(validity) <@ NEW.validity; + END IF; + RETURN NULL; -- ignored +END; +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS +$$ +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; + END IF; + RETURN NULL; -- ignored +END; +$$ +LANGUAGE plpgsql; + -- -- GEMMA data -- @@ -296,6 +333,9 @@ CREATE UNIQUE INDEX gauges_erased_unique_constraint ON gauges (location) WHERE NOT erased + -- Associate referencing objects to matching gauge version + CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity + ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing() CREATE TABLE gauges_reference_water_levels ( location isrs NOT NULL, @@ -525,6 +565,10 @@ CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint ON bottlenecks (bottleneck_id) WHERE NOT erased + -- 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) diff -r 58508f50d192 -r 29ef6d41e4af schema/gemma_tests.sql --- a/schema/gemma_tests.sql Fri Jun 14 17:56:42 2019 +0200 +++ b/schema/gemma_tests.sql Sat Jun 15 09:24:28 2019 +0200 @@ -31,3 +31,41 @@ 23505, NULL, 'No duplicate geometries can be inserted into waterway_area'); +START TRANSACTION; +CREATE TEMP TABLE new_v (v) AS + SELECT tstzrange(current_timestamp - '2 d'::interval, + current_timestamp - '12 h'::interval); +INSERT INTO waterway.gauges ( + location, + validity, + objname, + geom, + zero_point, + date_info, + source_organization, + lastupdate, + erased) +VALUES ( + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, + (SELECT v FROM new_v), + 'testgauge', + ST_geomfromtext('POINT(0 0)', 4326), + 0, + current_timestamp, + 'testorganization', + current_timestamp, + true); +-- Fix validity of old entry to match exclusion constraint +UPDATE waterway.gauges SET + validity = validity - (SELECT v FROM new_v) +WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs + AND validity && (SELECT v FROM new_v) + AND NOT erased; +COMMIT; +SELECT results_eq($$ + SELECT DISTINCT gauge_validity FROM waterway.bottlenecks + $$, + $$ + SELECT v FROM new_v + $$, + 'Bottlenecks have been associated to new matching gauge version'); diff -r 58508f50d192 -r 29ef6d41e4af schema/run_tests.sh --- a/schema/run_tests.sh Fri Jun 14 17:56:42 2019 +0200 +++ b/schema/run_tests.sh Sat Jun 15 09:24:28 2019 +0200 @@ -28,7 +28,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c "SELECT plan(70 + ( + -c "SELECT plan(71 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \