Mercurial > gemma

annotate schema/updates/1405/01.add_fm_validity.sql @ 5030:737d7859dd86

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store fairway dimensions as MultiPolygon This avoids storing a single invalid geometry from the data source as multiple valid geometries with duplicate attribute sets. The previous behaviour was not correctly handled in import tracking, because only the ID of the first item in a set of multiple geometries generated from a single entry from the data source was tracked.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Mar 2020 18:42:30 +0100
parents 7cc79c65a9e5
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.fairway_marks_new (
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 CHECK (NOT isempty(validity)),
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp,
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 LIKE waterway.fairway_marks
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 );
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 DO LANGUAGE plpgsql
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 $$
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 DECLARE table_suffix varchar;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 BEGIN
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 /* Re-write tables inheriting from fairway_marks to include new columns
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 in correct position */
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 FOREACH table_suffix IN ARRAY ARRAY[
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp',
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk']
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 LOOP
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 'INHERITS (waterway.fairway_marks_new)', table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 /* Drop index referring to wrong columns. See
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 */
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx',
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 'FROM waterway.fairway_marks_%1$s', table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE',
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 'RENAME TO fairway_marks_%1$s', table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 END LOOP;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 /* Recreate foreign key constraints */
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk']
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 LOOP
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 'ADD FOREIGN KEY (fm_%1$s_id) '
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 'REFERENCES waterway.fairway_marks_%1$s', table_suffix);
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 END LOOP;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 END;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 $$;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 DROP TABLE waterway.fairway_marks;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 -- Restore GRANTs
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 GRANT SELECT on ALL tables in schema waterway TO waterway_user ;
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 TO waterway_admin;