Updating Evergreen for Newer PostgreSQL Versions

Bug #1937294 reported by Jason Stephenson
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Undecided
Unassigned

Bug Description

As of this bug report, the Evergreen community only supports Evergreen with PostgreSQL versions 9.6 and 10. According to the PostgreSQL Versioning Policy (https://www.postgresql.org/support/versioning/), the PostgreSQL community will expire support for these versions in November 2021 and November 2022, respectively. While the various Linux distributions may continue to supply security patches on these versions well past those dates, we should not rely on that. Furthermore, the recent tendency has been to install PostgreSQL packages using the PostgreSQL community apt repository (https://wiki.postgresql.org/wiki/Apt) where available.

Since the Evergreen-supported versions of PostgreSQL are soon to be out of support, and since PostgreSQL versions 11, 12, and 13 have all been available for some time, it would behoove us to begin investigating a move to these newer PostgreSQL releases. To that end, Jason Stephenson of CW MARS has started a project to test the Evergreen database on PostgreSQL versions 10 through 13 (and possibly more recent versions as they are ready). The results of this testing will be posted to files in the shared Google Drive folder listed below:

https://drive.google.com/drive/folders/1sRZ8P1RHCOcZx42DxUehvOpoNkJJnfqn?usp=sharing

Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

tags: added: database
Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

Revision history for this message
Evergreen Bug Maintenance (bugmaster) wrote :

What is the undefined behavior in question?

Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

Revision history for this message
Jason Stephenson (jstephenson) wrote :

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.

Changed in evergreen:
status: New → In Progress
milestone: none → 3.9-beta
Revision history for this message
Jason Stephenson (jstephenson) wrote :
Changed in evergreen:
assignee: Jason Stephenson (jstephenson) → nobody
status: In Progress → New
tags: added: pullrequest
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers