--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1405/01.add_fm_validity.sql	Wed Feb 26 12:18:15 2020 +0100
@@ -0,0 +1,51 @@
+CREATE TABLE waterway.fairway_marks_new (
+    validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
+        CHECK (NOT isempty(validity)),
+    last_found timestamp with time zone NOT NULL DEFAULT current_timestamp,
+    LIKE waterway.fairway_marks
+);
+
+DO LANGUAGE plpgsql
+$$
+DECLARE table_suffix varchar;
+BEGIN
+    /* Re-write tables inheriting from fairway_marks to include new columns
+       in correct position */
+    FOREACH table_suffix IN ARRAY ARRAY[
+        'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp',
+        'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk']
+    LOOP
+        EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new '
+            '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) '
+            'INHERITS (waterway.fairway_marks_new)', table_suffix);
+        /* Drop index referring to wrong columns. See
+           https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org
+         */
+        EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx',
+            table_suffix);
+        EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new '
+            'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * '
+            'FROM waterway.fairway_marks_%1$s', table_suffix);
+        EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE',
+            table_suffix);
+        EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new '
+            'RENAME TO fairway_marks_%1$s', table_suffix);
+    END LOOP;
+
+    /* Recreate foreign key constraints */
+    FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk']
+    LOOP
+        EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps '
+            'ADD FOREIGN KEY (fm_%1$s_id) '
+            'REFERENCES waterway.fairway_marks_%1$s', table_suffix);
+    END LOOP;
+END;
+$$;
+
+DROP TABLE waterway.fairway_marks;
+ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks;
+
+-- Restore GRANTs
+GRANT SELECT on ALL tables in schema waterway TO waterway_user ;
+GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
+    TO waterway_admin;