Mercurial > gemma
diff schema/gemma_tests.sql @ 5141:722b7c305319
Prevent equal axis geometries with intersecting validity
Such things usually cannot happen via importing, but it is at least
consistent with constraints on other tables and the functionality
is needed for fairway dimensions, since they are first stored in the
staging area and equal geometries with intersecting validity could
be generated by a concurrent import.
Set the CONSTRAINT TRIGGER as INITIALLY DEFERRED because new entries
are inserted before the validity of existing entries is adapted
in the same transaction.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 31 Mar 2020 12:50:30 +0200 |
| parents | e21cbb9768a2 |
| children |
line wrap: on
line diff
--- a/schema/gemma_tests.sql Tue Mar 31 10:52:26 2020 +0200 +++ b/schema/gemma_tests.sql Tue Mar 31 12:50:30 2020 +0200 @@ -25,6 +25,7 @@ 'NULL value is not checked'); SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'), (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test') @@ -32,6 +33,21 @@ 23505, NULL, 'No duplicate geometries can be inserted into waterway_axis'); +SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; + INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(NULL, current_timestamp) + ), ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(current_timestamp - interval '1d', NULL) + ) + $$, + 23505, NULL, + 'Duplicate axis geometries cannot be inserted if validities intersect'); + SELECT lives_ok($$ INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),