[OPEN-ILS-DEV] EG conference discussion topics

[Galen Charlton]

Hi,

On Apr 22, 2011, at 2:32 PM, Mike Rylander wrote:
> [4] Sketch a plan for creating more fault-tolerant upgrade SQL scripts
> 
> There's a lot of pain involved in upgrades when the SQL is largely a
> single transaction and some of the things it wants to do are already
> done in the target DB.  The 1.6-2.0 script is particularly painful in
> this way.

Agreed.

> One thought is to extend our current use of config.upgrade_log with a
> function call instead of direct inserts, check if the chunk in
> question is already applied, and if so then raise a notice saying as
> much.  That would let us simply concatenate the Pg/upgrade/ dir into a
> single script that applies what's in there, skips "we already have
> that" exceptions, and will stop on errors encountered during one small
> chunk so that it can be addressed manually.  Or, we could adopt the
> upgrade_db.sh script used for dev environments, which does most of
> this already ... but may be somewhat less robust than an all-in-db
> version (or, it feels that way, a bit, to me).

Good ideas.  One thing I suggest adding to this mechanism is a bit of tagging so that we can readily replace a set of point SQL upgrades with a consolidated step.  That would allow the upgrade process to bypass churn (0901: add index, 0902: no, we don't need it, drop it) or combine slow updates into a faster hand-crafted one.

Another weakness that should be fixed is the fact that stored procedure definitions are duplicated at least once, and often twice when they change, which exposes us to the possibility that you get one version of a function if you create a database from scratch and a different one if you upgrade from a previous version.  If we can reduce the upgrade step for stored procedure versions to "reload this function definition from the singular file where it is defined", it would eliminate a vector for bugs.

Regards,

Galen
--
Galen Charlton
VP, Data Services
Equinox Software, Inc. / Your Library's Guide to Open Source
email:  gmc at esilibrary.com
direct: +1 352-215-7548
skype:  gmcharlt
web:    http://www.esilibrary.com/