01-add-foreign-keys.sql delete statements causing data loss after mariadb migration

[amyfromandi]

Overview

While reviewing production vs. staging vs. development row counts for strat_tree , we identified multiple row mismatches caused by delete statements embedded inside the 01-add-foreign-keys.sql migration. These deletes appear to remove data leading to inconsistencies across the macrostrat schema in development and the macrostratbak2 schema (the restored backup saved after the Mariadb migration was ran). We need to review the data being removed to determine whether removal is valid, or if we are losing needed data.

Tables with row count variations:

col_refs
unit_econs
unit_environs
unit_strat_names
strat_names
strat_names_places
timescales_intervals
units
units_sections
strat_tree

Row count discrepancies:

table_name	macrostrat_count	macrostratbak2_count	diff
col_refs	5648	5716	-68
strat_names	51225	51229	-4
strat_names_places	50288	50321	-33
strat_tree	29467	29467	0
timescales_intervals	2194	2195	-1
unit_econs	3148	3157	-9
unit_environs	139193	140808	-1615
units	133417	133419	-2
units_sections	133675	133671	4
unit_strat_names	28319	28322	-3

strat_tree variance is 0 now after the macrostrat.strat_tree table was dropped and restored with CREATE TABLE macrostrat.strat_tree AS SELECT * FROM macrostratbak2.strat_tree

Next steps

• Restore macrostrat tables from macrostratbak2
• Review each DELETE statment to determine if the data deletions are necessary

[amyfromandi]

Here is the query I used to execute:

WITH table_list AS (
  SELECT unnest(ARRAY[
    'col_refs',
    'unit_econs',
    'unit_environs',
    'unit_strat_names',
    'strat_names',
    'strat_names_places',
    'timescales_intervals',
    'units',
    'units_sections',
    'strat_tree'
  ]) AS table_name
),
counts AS (
  SELECT
    t.table_name,
    (SELECT COUNT(*) FROM macrostrat.col_refs               WHERE t.table_name = 'col_refs')
      + (SELECT COUNT(*) FROM macrostrat.unit_econs         WHERE t.table_name = 'unit_econs')
      + (SELECT COUNT(*) FROM macrostrat.unit_environs      WHERE t.table_name = 'unit_environs')
      + (SELECT COUNT(*) FROM macrostrat.unit_strat_names   WHERE t.table_name = 'unit_strat_names')
      + (SELECT COUNT(*) FROM macrostrat.strat_names        WHERE t.table_name = 'strat_names')
      + (SELECT COUNT(*) FROM macrostrat.strat_names_places WHERE t.table_name = 'strat_names_places')
      + (SELECT COUNT(*) FROM macrostrat.timescales_intervals WHERE t.table_name = 'timescales_intervals')
      + (SELECT COUNT(*) FROM macrostrat.units              WHERE t.table_name = 'units')
      + (SELECT COUNT(*) FROM macrostrat.units_sections     WHERE t.table_name = 'units_sections')
      + (SELECT COUNT(*) FROM macrostrat.strat_tree         WHERE t.table_name = 'strat_tree')
      AS macrostrat_count,

    (SELECT COUNT(*) FROM macrostratbak2.col_refs               WHERE t.table_name = 'col_refs')
      + (SELECT COUNT(*) FROM macrostratbak2.unit_econs         WHERE t.table_name = 'unit_econs')
      + (SELECT COUNT(*) FROM macrostratbak2.unit_environs      WHERE t.table_name = 'unit_environs')
      + (SELECT COUNT(*) FROM macrostratbak2.unit_strat_names   WHERE t.table_name = 'unit_strat_names')
      + (SELECT COUNT(*) FROM macrostratbak2.strat_names        WHERE t.table_name = 'strat_names')
      + (SELECT COUNT(*) FROM macrostratbak2.strat_names_places WHERE t.table_name = 'strat_names_places')
      + (SELECT COUNT(*) FROM macrostratbak2.timescales_intervals WHERE t.table_name = 'timescales_intervals')
      + (SELECT COUNT(*) FROM macrostratbak2.units              WHERE t.table_name = 'units')
      + (SELECT COUNT(*) FROM macrostratbak2.units_sections     WHERE t.table_name = 'units_sections')
      + (SELECT COUNT(*) FROM macrostratbak2.strat_tree         WHERE t.table_name = 'strat_tree')
      AS macrostratbak2_count
  FROM table_list t
)
SELECT
  table_name,
  macrostrat_count,
  macrostratbak2_count,
  macrostrat_count - macrostratbak2_count AS diff
FROM counts
ORDER BY table_name;

[davenquinn]

Just re-ran the queries in all environments.

Development

table_name	macrostrat_count	macrostratbak2_count	diff
col_refs	5648	5716	-68
strat_names	51225	51229	-4
strat_names_places	50288	50321	-33
strat_tree	29467	29467	0
timescales_intervals	2194	2195	-1
unit_econs	3148	3157	-9
unit_environs	139193	140808	-1615
units	133417	133419	-2
units_sections	133675	133671	4
unit_strat_names	28319	28322	-3

Staging

table_name	macrostrat_count	macrostratbak2_count	diff
col_refs	5648	5716	-68
strat_names	51225	51229	-4
strat_names_places	50288	50321	-33
strat_tree	29467	29467	0
timescales_intervals	2194	2195	-1
unit_econs	3148	3157	-9
unit_environs	139193	140808	-1615
units	133417	133419	-2
units_sections	133675	133671	4
unit_strat_names	28319	28322	-3

Production

table_name	macrostrat_count	macrostratbak2_count	diff
col_refs	5648	5648	0
strat_names	51225	51225	0
strat_names_places	50288	50288	0
strat_tree	29467	29467	0
timescales_intervals	2194	2193	1
unit_econs	3148	3148	0
unit_environs	139193	133002	6191
units	133417	127227	6190
units_sections	133675	127485	6190
unit_strat_names	28319	28319	0

[davenquinn]

Looks like everything is aligned except that we are working with an older (or otherwise less complete) version of the macrostrat_bak2 schema in production.

• For the macrostrat schema, row counts align across all environments, which is good.
• Relative to the backups in development and staging, most of the removed rows are related to removing links that reference a missing entity.
• units_sections added rows reference new sections that were added for a few columns that were missing them.

These changes were made to improve the referential integrity of the Macrostrat database in advance of adding column editing functionality.

It would be helpful to copy the macrostrat_bak2 schema from staging to production. We should also rename it something very clear, such as macrostrat_v1_backup. That way, we can ensure that we have the same "baseline" across all environments.

[davenquinn]

In the future, it might be nice to have a general version of this SQL function in Python that works across schemas and environments.

[davenquinn]

I had to restore the macrostrat.strat_tree table in staging, and I did so based on the table in development, since I had already verified that one's contents. Here is the process, for posterity:

In development:

CREATE SCHEMA temp;
-- Put it in a temporary location so we don't overwrite the existing table on ingest
CREATE TABLE temp.strat_tree AS SELECT * FROM macrostrat.strat_tree;

macrostrat db dump macrostrat-strat-tree.pg-dump -- --table=temp.strat_tree
macrostrat db psql -c "DROP TABLE temp.strat_tree"

In staging:

macrostrat db psql -c "CREATE SCHEMA temp"
macrostrat db restore macrostrat-strat-tree.pg-dump

/* Remove constraints we've created on the `macrostrat.strat_tree` table,
which prevent us from inserting new rows with invalid refs or parent-child relationships */
ALTER TABLE macrostrat.strat_tree DROP CONSTRAINT IF EXISTS strat_tree_refs_fk;
ALTER TABLE macrostrat.strat_tree DROP CONSTRAINT IF EXISTS strat_tree_strat_names_child_fk;
-- Load the data
TRUNCATE TABLE macrostrat.strat_tree;
INSERT INTO macrostrat.strat_tree SELECT * FROM temp.strat_tree;
DROP TABLE temp.strat_tree;

This process might suggest more general approaches for moving data between environments.

[davenquinn]

https://github.com/pavlospt/rust-pgdatadiff