QA » openQA Project

I'm working on a PR that will involve adding two new columns to the jobs table (I want to add videos_present and results_present booleans so _ensure_results_below_threshold can be a bit more targeted on later runs). But it seems it's not really possible to add columns to that table ATM, because it causes the deploy.t test to fail on the 92-93 migration.

Two of the scripts in that migration - dbicdh/_common/upgrade/92-93/001-migrate-jobs.pl and 002-set-finished-and-fix-state.pl - do resultset queries on the jobs table and then use the instances that are returned. The problem is that this will always use the current state of the code, and if the code now references some new columns that will only be added later in the migration process, that causes the migration code to fail, like this:

failed to run Perl in /builddir/build/BUILD/openQA-0864455630bf2987681b56e57803a99bae73d742/t/../dbicdh/_common/upgrade/92-93/001-migrate-jobs.pl: DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute failed: ERROR:  column me.videos_present does not exist
LINE 1: ...id, me.t_started, me.t_finished, me.logs_present, me.videos_...
                                                             ^ [for Statement "SELECT me.id, me.result_dir, me.archived, me.state, me.priority, me.result, me.reason, me.clone_id, me.blocked_by_id, me.TEST, me.DISTRI, me.VERSION, me.FLAVOR, me.ARCH, me.BUILD, me.MACHINE, me.group_id, me.assigned_worker_id, me.t_started, me.t_finished, me.logs_present, me.videos_present, me.results_present, me.passed_module_count, me.failed_module_count, me.softfailed_module_count, me.skipped_module_count, me.externally_skipped_module_count, me.scheduled_product_id, me.result_size, me.t_created, me.t_updated FROM jobs me WHERE ( ( ( columns = ? OR columns = ? ) AND state = ? ) )" with ParamValues: 1='state', 2='blocked_by_id', 3='scheduled'] at /builddir/build/BUILD/openQA-0864455630bf2987681b56e57803a99bae73d742/t/../dbicdh/_common/upgrade/92-93/001-migrate-jobs.pl line 14

I've played with it a bit but can't find any way to deal with this, honestly. Limiting the queries in the migration scripts to only the relevant columns doesn't help - it seems the crash happens not when running the query but when iterating over the results. That is, with this code:

my $jobs = $schema->resultset('Jobs')->search({columns => [qw(state blocked_by_id)], state => 'scheduled'});

while (my $job = $jobs->next) {
    $job->calculate_blocked_by;
}

it crashes in the while (my $job = $jobs->next) { line, not the query itself.

We can't really rewrite the migration script as SQL or anything down those lines, because calculate_blocked_by is pretty complex logic.

So...I'm a bit roadblocked by this and I'm not sure what the best option is. Is our only option really to drop all schema versions before 93 from support before we can modify the jobs table again?

I could possibly rework my PR to work some other way (though this seems like the most logical way), but that only puts off the problem to whenever somebody else needs to change the table...