Mercurial > gemma
changeset 2130:f3aabc05f9b2
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