Hi, I'm fully +1 for everything other than functions, but with functions I have a strong preference for CREATE OR REPLACE so that we're doing all we can to install the version that the upgrade delivers. There are some upgrade-time risks for functions, because they're opaque and "defined" by just the name and param list (as Jason S notes), but I think covering all of the non-opaque object types with IF NOT EXISTS would be a big improvement.
Thanks, -- Mike Rylander Research and Development Manager Equinox Open Library Initiative 1-877-OPEN-ILS (673-6457) work: [email protected] personal: [email protected] https://equinoxOLI.org On Tue, Jun 13, 2023 at 11:09 AM Jason Boyer via Evergreen-dev <[email protected]> wrote: > > 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 > [email protected] > +1 (877) Open-ILS (673-6457) > https://equinoxOLI.org/ > > _______________________________________________ > Evergreen-dev mailing list > [email protected] > http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev _______________________________________________ Evergreen-dev mailing list [email protected] http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev
