# HG changeset patch # User Tom Gottfried # Date 1560356786 -7200 # Node ID 0ec5c8ec1e44e7c5b0fdc28dba63bfc9de952162 # Parent 123b9341408e72ef4c159d6cbe1fda7dcccfa09b Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens. diff -r 123b9341408e -r 0ec5c8ec1e44 pkg/imports/bn.go --- a/pkg/imports/bn.go Wed Jun 12 18:21:14 2019 +0200 +++ b/pkg/imports/bn.go Wed Jun 12 18:26:26 2019 +0200 @@ -49,8 +49,8 @@ erased = true WHERE bottleneck_id = $1 AND NOT erased - -- Don't touch old entry if validity did not change: will be updated - AND validity <> $2 + -- Don't touch old entry if new validity contains old: will be updated + AND NOT validity <@ $2 RETURNING 1 ) -- Decide whether a new version will be INSERTed @@ -142,7 +142,8 @@ revisiting_time = $10, limiting = $11, date_info = $12, - source_organization = $13 + source_organization = $13, + validity = $15 WHERE bottleneck_id = $1 AND NOT erased AND $12 > date_info @@ -442,19 +443,6 @@ feedback.Warn(handleError(err).Error()) 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, - bn.Bottleneck_id, - validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return err2 - } - return nil - } feedback.Info("insert new version") case !isNew: // try to update @@ -472,6 +460,7 @@ bn.Date_Info, bn.Source, tolerance, + &validity, ).Scan(&nid) switch { case err == sql.ErrNoRows: @@ -514,6 +503,20 @@ } } + // Set end of validity of old version to start of new version + // in case of overlap + if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx, + bn.Bottleneck_id, + validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return err2 + } + return nil + } + + // Insert riverbed materials if materials != nil { for _, mat := range materials { if _, err := tx.StmtContext(ctx, diff -r 123b9341408e -r 0ec5c8ec1e44 pkg/imports/wg.go --- a/pkg/imports/wg.go Wed Jun 12 18:21:14 2019 +0200 +++ b/pkg/imports/wg.go Wed Jun 12 18:26:26 2019 +0200 @@ -79,8 +79,8 @@ erased = true WHERE isrs_astext(location) = $1 AND NOT erased - -- Don't touch old entry if validity did not change: will be updated - AND validity <> $2 + -- Don't touch old entry if new validity contains old: will be updated + AND NOT validity <@ $2 RETURNING 1 ) -- Decide whether a new version will be INSERTed @@ -151,7 +151,8 @@ geodref = $12, date_info = $13, source_organization = $14, - lastupdate = $15 + lastupdate = $15, + validity = $16 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) AND NOT erased AND $15 > lastupdate @@ -375,44 +376,6 @@ unchanged++ continue } - // 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( - ctx, - code.String(), - &validity, - ); err != nil { - feedback.Warn(handleError(err).Error()) - if err2 := tx.Rollback(); err2 != nil { - return nil, err2 - } - unchanged++ - continue - } feedback.Info("insert new version") case !isNew: // try to update @@ -432,6 +395,7 @@ &dateInfo, source, time.Time(*dr.Lastupdate), + &validity, ).Scan(&dummy) switch { case err2 == sql.ErrNoRows: @@ -482,6 +446,46 @@ } } + // 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( + ctx, + code.String(), + &validity, + ); err != nil { + feedback.Warn(handleError(err).Error()) + if err2 := tx.Rollback(); err2 != nil { + return nil, err2 + } + unchanged++ + continue + } + // "Upsert" reference water levels for _, wl := range []struct { level **erdms.RisreflevelcodeType diff -r 123b9341408e -r 0ec5c8ec1e44 schema/gemma.sql --- a/schema/gemma.sql Wed Jun 12 18:21:14 2019 +0200 +++ b/schema/gemma.sql Wed Jun 12 18:26:26 2019 +0200 @@ -280,7 +280,7 @@ geom geography(POINT, 4326) NOT NULL, applicability_from_km int8, applicability_to_km int8, - validity tstzrange NOT NULL, + validity tstzrange NOT NULL CHECK (NOT isempty(validity)), zero_point double precision NOT NULL, geodref varchar, date_info timestamp with time zone NOT NULL, @@ -288,7 +288,6 @@ lastupdate timestamp with time zone NOT NULL, -- entry removed from external data source (RIS-Index)/historicised: erased boolean NOT NULL DEFAULT false, - CHECK (erased OR NOT isempty(validity)), PRIMARY KEY (location, validity), EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED @@ -489,7 +488,7 @@ CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, - validity tstzrange NOT NULL, + validity tstzrange NOT NULL CHECK (NOT isempty(validity)), EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED, gauge_location isrs NOT NULL, @@ -519,7 +518,6 @@ date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, erased boolean NOT NULL DEFAULT false, - CHECK (erased OR NOT isempty(validity)), staging_done boolean NOT NULL DEFAULT false ) -- Allow only one non-erased entry per bottleneck