Write a script to fix mis-allocated id numbers in database tables.

[gsrohde]

This was motivated by issue https://github.com/terra-mepp/data/issues/9.

The script would run SQL statements like this

SELECT setval('sites_id_seq', (SELECT MAX(id) FROM sites WHERE id / 1E9::int = 8), true);
UPDATE sites SET id = nextval('sites_id_seq') WHERE id / 1E9::int = 99;

In this example, we assume we are on machine 8 but that some ids in the sites table were allocated in the range reserved for machine 99. A similar update statement would be run for each problem table. (The setval should be run for all tables.)

Before running this script, however, we need to ensure that each table referring to the table we are updating (the sites table in this example) has the appropriate foreign-key constraint. In particular, the foreign-key constraint should have the "ON UPDATE CASCADE" clause. Using built-in DBMS methods of updating foreign key values is much cleaner than trying to have the script do it manually.

Lastly, before the appropriate foreign-key constraints can be added, there may be cases where some database cleanup is required before the constraints can be added.

@robkooper and @dlebauer, please comment.

[dlebauer]

That sounds like to me like it should work.

[gsrohde]

Implemented by PR #586 (see notes there).