Upgrading on postgres resets sequences for table ids

[dfraser]

If an upgrade recreates tables, then the sequences associated with those tables are recreated too, and their last_values need to be reset.

​Trac ticket #8575 documents the general problem.

The following query should fix bitten tables:

SELECT setval('bitten_build_id_seq', (SELECT MAX(id) FROM bitten_build)+1);
SELECT setval('bitten_log_id_seq', (SELECT MAX(id) FROM bitten_log)+1);
SELECT setval('bitten_platform_id_seq', (SELECT MAX(id) FROM bitten_platform)+1);
SELECT setval('bitten_report_id_seq', (SELECT MAX(id) FROM bitten_report)+1);

To get a list of sequences and tables in postgres,

select relname from pg_class where relkind = 'S';
select tablename from pg_tables where schemaname = 'public';

I'm sure we could work out a reasonably naive solution that runs a script after an upgrade on postgres, while waiting for the trac ticket.

[hodgestar+bitten@…]

Upgrade script tests, sequence updating and index dropping functions (version 1).

[hodgestar+bitten@…]

I've attached a patch that attempts to address the PostGreSQL and MySQL sequence update issues. In order to have some confidence in the solution, the patch also adds tests for the upgrade scripts and the minimal modifications to existing upgrade scripts to get the tests to pass.

This patch also adds index dropping support to deal with the issue reported in #461.

[hodgestar+bitten@…]

Updated patch. Removes unnecessary fixing of MySQL 'sequences'. Better cleaning up of data and transaction committing in tests.

[hodgestar]

Adds osimons patch to commit after each upgrade step from #462 and puts upgrade tests into test suite.

[hodgestar]

Adds test for doing upgrade via bitten.main.Build Setup?.upgrade_environment.

[hodgestar]

I think the main thing that is missing from the patch at the moment is improvements to the upgrade tests to:

• Include more data.
• Do better checking of the results at the end of the upgrade.

After that I probably need to carefully read through upgrades.py one more time before committing.

[osimons]

I think the patch looks great. Read through it, and it just feels much more solid that what is currently done in upgrades. Tests are a bit painful to write - and especially when making up data to try to catch real-life situations... You can always defer expanding test coverage for actual tickets that may arise later. Feel free to commit what you have whenever you like.

[hodgestar]

I've added a final version of the patch that:

• Adds a basic check of the contents of the database after the upgrade.
• Fixes a bug (picked up by the new tests) in earlier versions of the patch that caused log messages to have bad ids when upgrading to schema version 2 (this then resulted in log messages being totally lost when upgrading to version 8).

The change to main.py that commits after each upgrade function had a small oddity. If an upgrade had more than one function (currently upgrades to versions 5, 6, 8 and 10) then it would call db.commit() once for each function and print the 'Upgraded Bitten tables to version X' message multiple times. Perhaps more importantly a failed upgrade would then skip any remaining functions the next time the upgrade was run.

I've moved the commit, version update and print message one level out so they are run after each version and not after each function.

[hodgestar]

Check builds. Fix minor bug in log table upgrade detected by tests.

[hodgestar]

Patch committed to trunk in r786 and to branches/0.6.x in r787.