[Evergreen-dev] Thoughts on db upgrade scripts

[Mike Rylander]

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: miker at equinoxOLI.org
personal: mrylander at gmail.com
https://equinoxOLI.org

On Tue, Jun 13, 2023 at 11:09 AM Jason Boyer via Evergreen-dev
<evergreen-dev at list.evergreen-ils.org> 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
> JBoyer at equinoxOLI.org
> +1 (877) Open-ILS (673-6457)
> https://equinoxOLI.org/
>
> _______________________________________________
> Evergreen-dev mailing list
> Evergreen-dev at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev