Dave, to be honest, the easiest thing for me was to remove the offending upgrade from both the callbacks (as is the case with XoWIKI) or the upgrade directory. And whenever I stumble upon such a thing I silently commit to OpenACS on the latest release (as all future releases obviously would have the same problem).
Also note that we run into the same problem whenever we have dependencies on other packages (e.g. a change of PL/SQL function in acs-content-repository which the old upgrade script in acs-subsite does not know about).
Either way, I take from this discussion the following guidelines for the developers:
a) Use TCL callbacks to upgrade whenever possible, do not use the SQL upgrade directories
b) Wrap your TCL callbacks in a db_transaction (and yes I know you are saying this does not help when it fails, but at least I do not have to fix it in the database if all is rolled back).
c) Make sure you require the latest version of a certain package you depend on and check your upgrade scripts at least one major release back if it still works (read: are the procedures still there and is the DM still the same)
For the end users:
d) Make sure you upgrade every major release
e) Make sure to backup before running the upgrade
f) Do not upgrade from the acs-repository (as it fails for too many users it seems).
