Mercurial > gemma
changeset 3176:1cb6676d1510
Handle failing INSERTs gracefully during gauge measurements import
Makes obsolete the code duplicating constraint checking in the application.
Although automated constraint naming by PostgreSQL is predictable,
name the constraint used for trapping the error explicitly to
improve control and traceability.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 06 May 2019 18:18:17 +0200 |
parents | 296392d4539c |
children | fc008b32c593 |
files | pkg/imports/errors.go pkg/imports/gm.go schema/gemma.sql |
diffstat | 3 files changed, 23 insertions(+), 83 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/errors.go Mon May 06 17:45:24 2019 +0200 +++ b/pkg/imports/errors.go Mon May 06 18:18:17 2019 +0200 @@ -29,13 +29,25 @@ // Handle PostgreSQL error codes const ( - noDataFound = "P0002" + foreignKeyViolation = "23503" + noDataFound = "P0002" ) type dbError pgx.PgError func (err dbError) Error() string { switch err.Code { + case foreignKeyViolation: + switch err.SchemaName { + case "waterway": + switch err.TableName { + case "gauge_measurements": + switch err.ConstraintName { + case "gauge_key": + return "Referenced gauge is not in database" + } + } + } case noDataFound: // Most recent line from stacktrace contains name of failed function recent := strings.SplitN(err.Where, "\n", 1)[0]
--- a/pkg/imports/gm.go Mon May 06 17:45:24 2019 +0200 +++ b/pkg/imports/gm.go Mon May 06 18:18:17 2019 +0200 @@ -4,13 +4,14 @@ // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // -// Copyright (C) 2018 by via donau +// Copyright (C) 2018, 2019 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH // // Author(s): // * Raimund Renkert <raimund.renkert@intevation.de> // * Sascha L. Teichmann <sascha.teichmann@intevation.de> +// * Tom Gottfried <tom.gottfried@intevation.de> package imports @@ -18,7 +19,6 @@ "context" "database/sql" "fmt" - "sort" "strings" "time" @@ -40,16 +40,6 @@ const GMJobKind JobKind = "gm" const ( - listGaugesSQL = ` -SELECT - (location).country_code, - (location).locode, - (location).fairway_section, - (location).orc, - (location).hectometre -FROM waterway.gauges -WHERE (location).country_code = users.current_user_country()` - // Note: we do not expect corrections of data through this service. So // any constraint conflicts are triggered by actual redundat data which // can be dropped. @@ -164,39 +154,6 @@ ) } -func loadGauges(ctx context.Context, tx *sql.Tx) ([]string, error) { - - rows, err := tx.QueryContext(ctx, listGaugesSQL) - if err != nil { - return nil, err - } - defer rows.Close() - - var gauges []string - - for rows.Next() { - var g models.Isrs - if err = rows.Scan( - &g.CountryCode, - &g.LoCode, - &g.FairwaySection, - &g.Orc, - &g.Hectometre, - ); err != nil { - return nil, err - } - gauges = append(gauges, g.String()) - } - - if err = rows.Err(); err != nil { - return nil, err - } - - sort.Strings(gauges) - - return gauges, nil -} - func storeGaugeMeasurements( ctx context.Context, importID int64, @@ -207,37 +164,19 @@ start := time.Now() - tx, err := conn.BeginTx(ctx, nil) + // TODO get date_issue for selected gauges + gids, err := doForGM(ctx, fetch, conn, feedback) if err != nil { - return nil, err - } - defer tx.Rollback() - - // Get available gauges from database for use as filter in SOAP request - gauges, err := loadGauges(ctx, tx) - if err != nil { - return nil, err - } - - // TODO get date_issue for selected gauges - gids, err := doForGM(ctx, gauges, fetch, tx, feedback) - if err != nil { - feedback.Error("Error processing %d gauges: %v", len(gauges), err) + feedback.Error("Error processing gauges: %v", err) return nil, err } if len(gids) == 0 { - feedback.Info("No new gauge measurements found") return nil, UnchangedError("No new gauge measurements found") } - if err = tx.Commit(); err != nil { - feedback.Info( - "Importing gauge measurements failed after %s", time.Since(start)) - return nil, err - } feedback.Info( - "Importing gauge measurements successfully took %s", time.Since(start)) + "Importing gauge measurements took %s", time.Since(start)) // TODO: needs to be filled more useful. summary := struct { @@ -276,24 +215,17 @@ func doForGM( ctx context.Context, - gauges []string, fetch func() ([]*nts.RIS_Message_Type, error), - tx *sql.Tx, + conn *sql.Conn, feedback Feedback, ) ([]string, error) { - insertStmt, err := tx.PrepareContext(ctx, insertGMSQL) + insertStmt, err := conn.PrepareContext(ctx, insertGMSQL) if err != nil { return nil, err } defer insertStmt.Close() - // lookup to see if we have gauges in the database. - isKnown := func(s string) bool { - idx := sort.SearchStrings(gauges, s) - return idx < len(gauges) && gauges[idx] == s - } - result, err := fetch() if err != nil { return nil, err @@ -310,10 +242,6 @@ continue } feedback.Info("Found measurements for %s", curr) - if !isKnown(curr) { - feedback.Warn("Gauge '%s' is not in database.", curr) - continue - } var referenceCode string if wrm.Reference_code == nil { @@ -363,7 +291,7 @@ case err == sql.ErrNoRows: // thats expected, nothing to do case err != nil: - return nil, err + feedback.Warn(handleError(err).Error()) default: newCnt++ }
--- a/schema/gemma.sql Mon May 06 17:45:24 2019 +0200 +++ b/schema/gemma.sql Mon May 06 18:18:17 2019 +0200 @@ -297,7 +297,7 @@ CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - fk_gauge_id isrs NOT NULL REFERENCES gauges, + fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, -- TODO: add relations to stuff provided as enumerations