While working to upgrade Sloanspace from 4.6.3 to 5.0, the Sloan team ran into a bunch of problems that can in part be solved if we adopt two minor restrictions on upgrade scripts. The problems that were run into mostly affect those who don't track upgrades as they come out but rather delay, which requires leapfrogging several upgrades at one time. While people who do this are almost guaranteed to run into some problems we should do wall we can to simplify the process.
So:
1. (Oracle only) Upgrade scripts should include all PACKAGE declarations before any PACKAGE BODY declaration appears. Once PACKAGE (i.e. header) declarations are compiled, PACKAGE BODYs can be compiled regardless of dependencies without error. Andrew Grumet ran into about a half-dozen upgrade errors that were solved by this change.
2. (Oracle and PG) DO NOT include (@ or \i) entire SQL files from the /oracle or /postgresql directories in upgrade scripts! This only works in the general case if the SQL file is synchronized with the upgrade script, which is not true if you've skipped certain releases ... Andrew ran into a few upgrade problems related to this, too, and had to dig through older *-create.sql versions to dig out the right code to make the interim upgrade scripts work.
If the above is not clear please ask questions.