Missing db functions in 3.0.1-3.0.2 upgrade script

I set this to confirmed and high because I see the functions are missing from any upgrade scripts. That said, I seem to have all but the last one, evergreen.asset_copy_alert_copy_inh_fkey, in a database that was upgraded from 2.12.8 to 3.0.7 using the upgrade scripts. I don't know where the functions came from unless they were always there or were added to our production database at some point prior to the dump and test upgrade. I don't remember adding them, myself.

Also I am not sure that editing a published upgrade script is the best way to go. We may want to do that and create a new upgrade script for those who won't re-run the 3.0.1-3.0.2 upgrade.

Hello. We also seem to be missing a couple functions in our database. What is interesting is that for the 3.0 upgrade we only seem to be missing evergreen.container_copy_bucket_item_target_copy_inh_fkey() but we were NOT missing the evergreen.asset_* functions that Bill has in his patch.

For the 3.1 upgrade we were also missing oils_json_to_text()

I assumed this was some sort of brain damage that our database had accumulated over the years, added the functions back in (picking them out of the database creation scripts) and moved on.

Could something else strange be going on here?

A note on oils_json_to_text(). I had that in the public schema, which seems to date back to v2.0. I did copy it into the evergreen schema which is where it's expected in 3.1

I recall there being some discussion of these functions missing before. It turns out they are supposed to be created by the 1063 upgrade script in 2.12.6 to 3.0.0. Looking at the code in 1063, it seems to me that the new functions will not be created if your database lacks the prerequisite constraints on asset.copy_pkey.

So, I'm less certain this is a confirmed bug or a data issue, but I'll not change it for now.

Hmmm, our database has this:

CONSTRAINT copy_pkey PRIMARY KEY (id),

in asset.copy.

In our 2.12 production database, the information_schema.referential_constraints has two rows with unique_constraint_name = copy_pkey. One with constraint_name = asset_copy_note_copy_fkey The other has constraint_name = import_item_imported_as_fkey

Those unique_constraint_name = copy_pkey rows are gone in our test databases that have been upgraded to 3.0 and 3.1.

I am running into this problem as well. Upgrade script 1063 does not outright create the functions. It runs a query that should return data that is then used as variables to create the functions. My 2.12.4 system built in 2012 and upgraded ever since returns no rows. A 2.12.0 system built recently returns 3 rows and creates the needed functions.

So I think the success of 3.0.1-3.0.2 depends on how old your system is. The missing functions were added to 070.schema.container.sql and 800.fkeys.sql since April 2017.

I like Bill's update to script 1081 that explicitly creates or updates the functions.

It looks like the vandelay.import_item imported_as_fkey constraint was added to the 2.2 release, but wasn't included in the upgrade scripts. So possibly those that have a pre 2.2 database, may run into at least that one?

Josh

It does feel a little weird to push these into the upgrade script, but really, either you had these functions and upgrade worked, or you didn't and it didn't, and in either case, adding them in here is fine. I also agree with the general sentiment that recreating functions as they already may exist does no harm.

So, pushed to master through rel_3_1. Better late then never; thanks, Bill!