2.5.2-2.5.3-upgrade-db.sql and ERROR: column "behind_desk" does not exist

Bug #1287510 reported by Jason Etheridge on 2014-03-04
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
High
Unassigned
2.5
Undecided
Unassigned

Bug Description

In 0822.schema.per-hold-behind-desk.sql, we add the behind_desk column to action.hold_request, but not to action.aged_hold_request. This doesn't affect a pristine database install using 090.schema.action.sql, because there action.aged_hold_request is built with LIKE action.hold_request, but that construct isn't like INHERIT, so new columns on action.hold_request won't magically appear on action.aged_hold_request

So somewhere we need an ALTER TABLE action.aged_hold_request
    ADD COLUMN behind_desk BOOLEAN NOT NULL DEFAULT FALSE;

Jason Etheridge (phasefx) wrote :

This also affects 0868.schema.all_hold_behind_desk.sql

Dan Wells (dbw2) wrote :

I don't think there is a straightforward way to fix cases like this where we end up with two distinct databases in the wild (one with the column, one without).

I propose that we add the ALTER TABLE to the top of these two upgrade files, outside the transaction, with a notice saying it is alright if it fails. Anyone who was able to run these upgrades successfully must already have this column, so going this route should help those who need it while not harming those who don't need it.

Then we re-roll a 2.5.3"b" with the fix in place. Thoughts?

Mike Rylander (mrylander) wrote :

I would recommend /not/ assigning a default on aged holds table, for two reasons: it avoids a table rewrite (speed) and it's inventing information that isn't necessarily true.

Mike Rylander (mrylander) wrote :

Hit "post" too soon ...

Otherwise, that sounds fine and sane.

Ben Shum (bshum) on 2014-03-04
Changed in evergreen:
status: New → Confirmed
importance: Undecided → High
Galen Charlton (gmc) wrote :

Following up on discussion on the IRC list, and in the interests of being pedantic: the full version of Mike's suggestion works out to action.aged_hold_request being a /nullable/ Boolean with no default value set.

Galen Charlton (gmc) wrote :

action.aged_hold_request.behind_desk, that is.

Galen Charlton (gmc) wrote :

Also, +1 to that suggestion.

Dan Wells (dbw2) wrote :

The 2.5.2-2.5.3 upgrade hasn't been pushed upstream yet, so all that is needed for master is the 0868 file:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dbwells/lp1287510_missing_behind_desk

Once this is pushed, I'll get the whole corrected 2.5.2-2.5.3 file in the various places it needs to be.

tags: added: pullrequest
Changed in evergreen:
milestone: none → 2.6.0-rc1
Ben Shum (bshum) wrote :

Pushed dbwells fix to master and rel_2_5

Changed in evergreen:
status: Confirmed → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers