Updating Evergreen for Newer PostgreSQL Versions

The following pgtap tests fail when run on PostgreSQL version 12 through 14:

t/authority-update-bib-propagation.pg (Wstat: 0 Tests: 9 Failed: 4)
  Failed tests: 1-3, 7
t/lp1501781-unaccent_and_squash.pg (Wstat: 0 Tests: 18 Failed: 2)
  Failed tests: 7, 12
t/lp1731960_test_preserving_bookbag_entries.pg (Wstat: 0 Tests: 2 Failed: 1)
  Failed test: 1
t/regress/lp1379815_vl_import_item_stat_cats.pg (Wstat: 0 Tests: 1 Failed: 1)
  Failed test: 1
live_t/0824.item_import_defaults.pg (Wstat: 0 Tests: 1 Failed: 1)
  Failed test: 1
live_t/0847.auth_overlay_generator.pg (Wstat: 0 Tests: 1 Failed: 1)
  Failed test: 1
live_t/lp1145213_test_func_asset.merge_record_assets.pg (Wstat: 0 Tests: 5 Failed: 1)
  Failed test: 1

A subset of the above tests fail on PostgreSQL 11, but the above list includes all of the tests that also fail on Pg 11.

Fixing the Evergreen code so that these tests succeed on PostgreSQL versions 11 through 14 and continue to work on PostgreSQL 10 would be the logical next step in getting Evergreen working on more recent PostgreSQL version.

I think it is also worth pointing bug 1947595 which is directly related to this one, but is its own bug because it is a discrete issue with a simple fix.

The live_t/20-hold-targeter.t Perl test fails on all PostgreSQL versions 11 - 14. It succeeds on PostgreSQL 9.6 and 10:

# Failed test 'Hold 263 has 24 mapped potential copies'
# at live_t/20-hold-targeter.t line 100.
# got: '21'
# expected: '24'
live_t/20-hold-targeter.t (Wstat: 256 Tests: 18 Failed: 1)
  Failed test: 11
  Non-zero exit status: 1

The other Perl tests pass.

It tuns out that at least the hold targeter test fails because the way we load test data depends on undefined behavior in PostgreSQL that has not changed from Pg 9.4 through Pg 10. Unfortunately, for us, that behavior has changed in Pg 11.

We need a more robust way to load the test data. I am inclined to starting by dumping the data from fresh loads on Pg 10 and using the resulting copy files.

The big issue with this will be keeping the test data up to date with schema changes. It may require developers to load the test database schema, run the db upgrade scripts, and then dump the data again. This could be scripted/programmed.

What is the undefined behavior in question?

As a specific example, what we're doing to create call numbers, copies, and assign copy locations gives different results on Pg11+ vs. Pg9.6 and Pg10:

http://irc.evergreen-ils.org/evergreen/2021-11-10#i_495073

When I asked about it in #postgresql, I was told that we need a more deterministic way of inserting our test data that doesn't rely on undefined behavior.

I want to reiterate that a big part of the problem is that our tests depend on the behavior of the scripts used to load the sample data. The behavior of these scripts changes with Pg11 and later PostgreSQL versions. This is what the folks in the PostgreSQL IRC channel said is us relying on undefined behavior.

I specifically encountered an example of this when looking into the failure of the hold targeter test mentioned in comment #3.

Branch is here: working/user/dyrcona/lp1937294-support-recent-postgresql

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp1937294-support-recent-postgresql

I have rebased the branch on master and force pushed it to the same location now that the fix for bug 1947595 has gone in.

I have rebased the branch on master again and force pushed it to the same location.

I think it behooves us to get this in before the alpha release of 3.9 so that issues may be resolved, if any.

I agree, this should go in ASAP for bug squashing. It looks good to me, so pushing it now!