V2 Dev migration applications to the Prod V1 data dump

[mwestphall]

While the migration system now applies successfully to a schema-only dump of the prod db (https://github.com/UW-Macrostrat/macrostrat/issues/70), several new issues arise when applying to a data dump:

macrostrat-core-v2

UPDATE macrostrat.strat_names SET ref_id = NULL WHERE ref_id = 0;

null value in column "ref_id" of relation "strat_names" violates not-null constraint

• This is already noted as causing an error

ALTER TABLE macrostrat.cols
    ADD CONSTRAINT cols_project_fk  FOREIGN KEY (project_id) REFERENCES macrostrat.projects(id) ON DELETE CASCADE;

insert or update on table "cols" violates foreign key constraint "cols_project_fk"
DETAIL:  Key (project_id)=(5) is not present in table "projects".

• This is due to the projects table being empty in the prod schema. We will need to complete the mariadb dump first

UPDATE macrostrat.col_groups cg SET project_id = c.project_id FROM macrostrat.cols c WHERE c.col_group_id = cg.id;

permission denied for schema macrostrat
LINE 1: SELECT 1 FROM ONLY "macrostrat"."projects" x WHERE "id" OPER...
                           ^
QUERY:  SELECT 1 FROM ONLY "macrostrat"."projects" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

• More research required for this one

partition-maps

ALTER TABLE maps.lines ADD CONSTRAINT maps_lines_geom_check CHECK (maps.lines_geom_is_valid(geom));
check constraint "maps_lines_geom_check" of relation "lines_large" is violated by some row

• More research required for this one

partition-carto

ALTER TABLE carto.lines ATTACH PARTITION carto.lines_large FOR VALUES IN ('large');
insert or update on table "lines_large" violates foreign key constraint "lines_source_id_fkey"
DETAIL:  Key (source_id)=(145) is not present in table "sources".

• Missing entity with id=145 in maps.sources

ALTER TABLE carto.polygons ADD CONSTRAINT polygons_pkey PRIMARY KEY (map_id, scale);
could not create unique index "polygons_tiny_pkey"
DETAIL:  Key (map_id, scale)=(3189765, tiny) is duplicated.

• Duplicate primary key

[davenquinn]

I have made some "ad hoc migrations" that solve some of these data issues. In particular the "Carto" rows being duplicated is one we have solved previously.

[mwestphall]

I was able to resolve the following issues with a combination of the ad-hoc migrations and borrowing missing data from dev:

macrostrat-core-v2

ALTER TABLE macrostrat.cols
    ADD CONSTRAINT cols_project_fk  FOREIGN KEY (project_id) REFERENCES macrostrat.projects(id) ON DELETE CASCADE;

• Can be fixed by importing a dump of the "macrostrat.projects" from dev

partition-carto

ALTER TABLE carto.lines ATTACH PARTITION carto.lines_large FOR VALUES IN ('large');
insert or update on table "lines_large" violates foreign key constraint "lines_source_id_fkey"

• Can be fixed by copying slug with id 145 ("unknown slug") from dev to prod

ALTER TABLE carto.lines ATTACH PARTITION carto.lines_large FOR VALUES IN ('large');
insert or update on table "lines_large" violates foreign key constraint "lines_source_id_fkey"

• Can be fixed by running ad-hoc migration carto-rebuild.sql

partition-maps

ALTER TABLE carto.polygons ADD CONSTRAINT polygons_pkey PRIMARY KEY (map_id, scale);
could not create unique index "polygons_tiny_pkey"

• Can be fixed by running carto-rebuild.sql with the following modifications:

  diff --git a/cli/macrostrat/cli/ad-hoc-migrations/carto-rebuild.sql b/cli/macrostrat/cli/ad-hoc-migrations/carto-rebuild.sql
  index 9161264..8a74f69 100644
  --- a/cli/macrostrat/cli/ad-hoc-migrations/carto-rebuild.sql
  +++ b/cli/macrostrat/cli/ad-hoc-migrations/carto-rebuild.sql
  @@ -6,6 +6,7 @@ do that until we've removed the duplicates.
  */
  
  ALTER TABLE carto.polygons ADD COLUMN updated boolean NOT NULL DEFAULT false;
  +ALTER TABLE carto.polygons_large ADD COLUMN updated boolean NOT NULL DEFAULT false;
  
  INSERT INTO carto.polygons_tiny (map_id, source_id, geom_scale, geom, scale, updated)
  @@ -69,4 +70,5 @@ DELETE FROM carto.polygons_large WHERE NOT updated AND map_id IN (SELECT map_id
  
  /** Once we have reduced duplicate geometries, we can add a unique constraint */
  ALTER TABLE carto.polygons ADD CONSTRAINT polygons_pkey PRIMARY KEY (map_id, scale);
  -ALTER TABLE carto.polygons DROP COLUMN updated;
  \ No newline at end of file
  +ALTER TABLE carto.polygons_large DROP COLUMN updated;
  +ALTER TABLE carto.polygons DROP COLUMN updated;