[Evergreen-dev] Thoughts on db upgrade scripts

[Jason Boyer]

After doing this system admin thing for a while you'll frequently find situations where you've applied a fix to a bug that's still in progress (oh, this horrible perf issue just needs an index? Do it!) but when the upgrade script finally makes its way to a release the upgrade script fails because you have the index but not the upgrade_log entry. Bummer.

We regularly use the "CREATE OR REPLACE" construction for functions and views, who has thoughts about moving to a format where the fresh-install sql is the usual INSERT (), CREATE INDEX ..., etc. while upgrade scripts are the guarded versions of such:

INSERT INTO table (fields) SELECT (values) WHERE NOT EXISTS (SELECT values FROM table);
CREATE (INDEX,TABLE) IF NOT EXISTS ...;
ALTER TABLE (most options) ...;
etc.

I realize the immediate issue is further drift between the upgrade scripts and the seed data for INSERTs and UPDATEs though it shouldn't be too difficult to spot issues. (There's no reason to avoid IF NOT EXISTS in the seed data aside from churn caused by adding them.) It probably wouldn't hurt to re-work some of the enormous piles of INSERTs to also make seed data patches easier to manage, but that's just a separate thought I'm putting out there.

What say you lot?

Jason

-- 
Jason Boyer
Senior System Administrator
Equinox Open Library Initiative
JBoyer at equinoxOLI.org
+1 (877) Open-ILS (673-6457)
https://equinoxOLI.org/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20230613/46e947e2/attachment-0001.htm>