Edgewall Software
Modify

Opened 12 years ago

Closed 12 years ago

#452 closed defect (fixed)

Upgrading on postgres resets sequences for table ids

Reported by: dfraser Owned by: hodgestar
Priority: major Milestone: 0.6
Component: General Version: 0.6b2
Keywords: Cc: hodgestar@…
Operating System: BSD

Description

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.

Attachments (5)

upgrade-tests-v1.diff (13.7 KB) - added by hodgestar+bitten@… 12 years ago.
Upgrade script tests, sequence updating and index dropping functions (version 1).
upgrade-tests-v2.diff (13.9 KB) - added by hodgestar+bitten@… 12 years ago.
Updated patch. Removes unnecessary fixing of MySQL 'sequences'. Better cleaning up of data and transaction committing in tests.
upgrade-tests-v3.diff (15.7 KB) - added by hodgestar 12 years ago.
Adds osimons patch to commit after each upgrade step from #462 and puts upgrade tests into test suite.
upgrade-tests-v4.diff (16.1 KB) - added by hodgestar 12 years ago.
Adds test for doing upgrade via bitten.main.Build Setup?.upgrade_environment.
upgrade-tests-v5.diff (18.2 KB) - added by hodgestar 12 years ago.
Check builds. Fix minor bug in log table upgrade detected by tests.

Download all attachments as: .zip

Change History (11)

Changed 12 years ago by hodgestar+bitten@…

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

comment:1 Changed 12 years ago by 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.

Changed 12 years ago by hodgestar+bitten@…

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

comment:2 Changed 12 years ago by hodgestar

  • Owner set to hodgestar
  • Status changed from new to assigned

Changed 12 years ago by hodgestar

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

Changed 12 years ago by hodgestar

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

comment:3 Changed 12 years ago by 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.

comment:4 Changed 12 years ago by 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.

comment:5 Changed 12 years ago by 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.

Changed 12 years ago by hodgestar

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

comment:6 Changed 12 years ago by hodgestar

  • Resolution set to fixed
  • Status changed from assigned to closed

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

Add Comment

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain hodgestar.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.