Mercurial > gemma

changeset 2130:f3aabc05f9b2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix constraints on waterway profiles staging_done in the UNIQUE constraint had no effect, because the exclusion constraint prevented two rows with equal location and validity anyhow. Adding staging_done to the exclusion constraint makes the UNIQUE constraint checking only a corner case of what the exclusion constraint checks. Thus, remove the UNIQUE constraint. Casting staging_done to int is needed because there is no appropriate operator class for booleans. Casting to smallint or even bit would have been better (i.e. should result in smaller index size), but that would have required creating such a CAST, in addition.
author Tom Gottfried <tom@intevation.de>
date Wed, 06 Feb 2019 15:42:32 +0100
parents bc310a0b5bae
children c6a4990a1d93
files schema/gemma.sql
diffstat 1 files changed, 4 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Feb 06 15:35:30 2019 +0100
+++ b/schema/gemma.sql	Wed Feb 06 15:42:32 2019 +0100
@@ -400,7 +400,6 @@
         location isrs NOT NULL,
         geom geography(linestring, 4326), -- TODO: NOT NIL
         validity tstzrange,
-        EXCLUDE USING GIST (isrs_asText(location) WITH =, validity WITH &&),
         lnwl double precision,
         mwl double precision,
         hnwl double precision,
@@ -412,7 +411,10 @@
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE (location, validity, staging_done)
+        EXCLUDE USING GIST (
+            isrs_asText(location) WITH =,
+            validity WITH &&,
+            CAST(staging_done AS int) WITH =)
     )
     CREATE TRIGGER waterway_profiles_date_info
         BEFORE UPDATE ON waterway_profiles