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

[Jason Etheridge]

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)]

This also affects 0868.schema.all_hold_behind_desk.sql

[Dan Wells (dbw2)]

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)]

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)]

Hit "post" too soon ...

Otherwise, that sounds fine and sane.

[Galen Charlton (gmc)]

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)]

action.aged_hold_request.behind_desk, that is.

[Galen Charlton (gmc)]

Also, +1 to that suggestion.

[Dan Wells (dbw2)]

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.

[Ben Shum (bshum)]

Pushed dbwells fix to master and rel_2_5