Submitted Date	07 Nov 2023
Approved Date	11 Dec 2023
Status	APPROVED
Impact	LOW

Overrides/Supersedes

FOLIO-2145 - Getting issue details... STATUS
MODINVSTOR-791 - Getting issue details... STATUS

RFC

Provide links to all relevant RFC's

Stakeholders

@mention individual(s) who has vested interest in the DR. This helps us to identify who needs to be aware of the decision

Contributors

Julian Ladisch Florian Gleixner

Approvers

List TC members voted to approve the change

Background/Context

Poppy officially supports PostgreSQL 12. Support of this version ends November 14, 2024.

Once a year the PostgreSQL team releases a new major version of PostgreSQL. Versions 13, 14, 15 and 16 are available. Some institutions have already migrated away from version 12 to 13 or 14 without any problems.

Assumptions

Sysops prefer to avoid a PostgreSQL migration if possible.

If a PostgreSQL migration is needed, sysops prefer the latest version PostgreSQL version.

Constraints

PostgreSQL 16 at Amazon RDS for PostgreSQL will be available by March 2024 (assuming the same timing like PostgreSQL 15 that has been available as Amazon RDS since Februrary 28, 2023).

Multi-tenant installations migrate tenants on different days, for example 4 tenants per weekend. Therefore they require the previous flower release to run under the new PostgreSQL version.

On November 20, 2023 the TC decided that it's to late to bump the officially supported PostgreSQL version for Quesnelia to 15 because the Quesnelia development planning has already been completed.

Rationale

"pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases." There is no need to incrementally upgrade to 13, and then to 14, and then to 15, and then to 16.

Skipping versions 13, 14, and 15 reduces the sysops' and devops' workload and downtimes.

Migrating PostgreSQL from 12 to 13 for Quesnelia should be avoided when it only lasts four months until the next migration from 13 to 16 is required for Ramson.

There's a gap between the end of PostgreSQL 12 support on November 14, 2024 and end of Quesnelia support around December 2024. Sysops should migrate PostgreSQL from 12 to 16 for their Quesnalia installations by November 14, 2024.

It's likely that breaking changes in 13, 14, 15 or 16 don't affect FOLIO or are very easy to fix. This is the result of the analysis of all published breaking changes, see list below.

The 35 biggest FOLIO modules (out of 60 database accessing modules) have been tested against PostgreSQL 13, 14, 15 and 16 using the module tests (mvn verify). No regression was found. See list below.

Making both PostgreSQL 12 and 16 the officially supported PostgreSQL version for the Quesnelia FOLIO release means that developers can only use PostgreSQL features that run under both versions. Almost always PostgreSQL is backward compatible so that only PostgreSQL 12 is needed for the automated tests for Quesnelia (unit tests, snapshot enviroments, vagrant boxes, rancher environments).

To test PostgreSQL 16 compatibility a single run of the Karate tests and the module tests (DR-000037 - TESTCONTAINERS_POSTGRES_IMAGE) is sufficient. A good time might be the Quesnelia bug fest. Bug fest and reference environments for Quesnelia containue to use PostgreSQL 12, the PostgreSQL 16 compatibility tests run on separate installations (Rancher environment, GitHub Actions).

Institutions are free to use any of the not officially supported PostgreSQL versions 13, 14, and 15 for their Quesnelia installation at their own risk.

Summary for developers:

For Quesnelia keep PostgreSQL 12 for unit tests. Use only SQL features of PostgreSQL 12, don't use SQL features from the breaking changes list (see below) of PostgreSQL 13, 14, 15 and 16.
For Ramson use PostgreSQL 16 for unit tests.

Summary for devops:

For Quesnelia keep reference environments, rancher environments, bug fest environment, Vagrant boxes etc. on PostgreSQL 12.
For Ramson run reference environments, rancher environments, bug fest environment, Vagrant boxes etc. on PostgreSQL 16.

Summary for test manager:

For Quesnelia: Quesnelia bug fest runs on PostgreSQL 12. During the bug fest execute additional PostgreSQL 16 tests on separate installations:
- Run Karate test against a FOLIO Deployment with PostgreSQL 16.
- Run module tests using GitHub Actions, DR-000037 - TESTCONTAINERS_POSTGRES_IMAGE and PostgreSQL 16.
For Ramson: No additional tests are needed.

Summary for sysops using only officially supported PostgreSQL versions:

Keep PostgreSQL 12 while migrating tenants to Quesnelia.
Before migrating PostgreSQL from 12 to 16 all tenant must have been migrated to Quesnelia.
PostgreSQL must be migrated from 12 to 16 before migrating a tenant to Ramson and by November 14, 2024, whatever comes first.
No module or module configuration change is needed for the PostgreSQL migration.

Summary for sysops using not officially supported PostgreSQL versions:

There are no known issues running Poppy and Quesnelia with PostgreSQL 12, 13, 14, 15, or 16.
Because it's likely that Ramson modules use PostgreSQL 16 SQL features it's likely that PostgreSQL must be migrated to 16 before migrating a tenant to Ramson.
Note that the final PostgreSQL 12 release will be on November 14, 2024, no security fixes will be released after this date.
No module or module configuration change is needed for the PostgreSQL migration.

Decision

PostgreSQL 12 and 16 are the officially supported PostgreSQL server versions for the Quesnelia FOLIO release; PostgreSQL 12 server support ends November 14, 2024. Only PostgreSQL 12 SQL features are allowed for the Quesnelia FOLIO release; they must also work under PostgreSQL 16.

PostgreSQL 16 is the officially supported PostgreSQL version for the Ramson FOLIO release.

Implications

Pros
- See "Assumptions" and "Rationale" above
Cons
- Developers are restricted to PostgreSQL 12 features for Quesnelia.

Breaking changes analysis

13

https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.17.4

Change SIMILAR TO ... ESCAPE NULL to return NULL (Tom Lane)
This new behavior matches the SQL specification. Previously a null ESCAPE value was taken to mean using the default escape string (a backslash character). This also applies to substring(text FROM pattern ESCAPE text). The previous behavior has been retained in old views by keeping the original function unchanged.
FOLIO doesn't use ESCAPE NULL: https://github.com/search?q=org%3Afolio-org+%22escape+null%22&type=code
Make json[b]_to_tsvector() fully check the spelling of its string option (Dominik Czarnota)
FOLIO uses neither json_to_tsvector nor jsonb_to_tsvector: https://github.com/search?q=org%3Afolio-org+json_to_tsvector&type=code , https://github.com/search?q=org%3Afolio-org+jsonb_to_tsvector&type=code
Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)
Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
```
SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);
```
This is a database administration setting, FOLIO is not affected.
Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views (Euler Taveira)
Queries that join these views to pg_stat_activity and wish to see auxiliary processes will need to use left joins.
FOLIO joins neither pg_stat_ssl nor pg_stat_gssapi: https://github.com/search?q=org%3Afolio-org+pg_stat_ssl&type=code , https://github.com/search?q=org%3Afolio-org+pg_stat_gssapi&type=code
Rename various wait events to improve consistency (Fujii Masao, Tom Lane)
FOLIO doesn't use wait_event, reading it is a database administrator task: https://github.com/search?q=org%3Afolio-org+wait_event&type=code
Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao)
Previously it returned ALTER TABLE; now it returns ALTER FOREIGN TABLE.
FOLIO doesn't use ALTER FOREIGN TABLE: https://github.com/search?q=org%3Afolio-org+%22ALTER+FOREIGN+TABLE%22&type=code
Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao)
Previously it returned ALTER TABLE; now it returns ALTER MATERIALIZED VIEW.
FOLIO doesn't use ALTER MATERIALIZED VIEW: https://github.com/search?q=org%3Afolio-org+%22ALTER+MATERIALIZED+VIEW%22&type=code
Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao)
This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used wal_keep_segments, the following formula will give you an approximately equivalent setting:
```
wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB)
```
This is a database administration setting, FOLIO is not affected.
Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson)
FOLIO doesn't use CREATE OPERATOR CLASS: https://github.com/search?q=org%3Afolio-org+%22CREATE+OPERATOR+CLASS%22&type=code
Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson)
FOLIO doesn't use the MATCH FULL syntax: https://github.com/search?q=org%3Afolio-org+%22MATCH+FULL%22&type=code
Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson)
FOLIO doesn't use the INTERNALLENGTH syntax: https://github.com/search?q=org%3Afolio-org++INTERNALLENGTH&type=code
Remove support for upgrading unpackaged (pre-9.1) extensions (Tom Lane)
The FROM option of CREATE EXTENSION is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13.
FOLIO doesn't use the CREATE EXTENSION ... FROM syntax: https://github.com/search?q=org%3Afolio-org+%22CREATE+EXTENSION%22+FROM&type=code
Remove support for posixrules files in the timezone database (Tom Lane)
IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.
FOLIO uses UTC in the database and therefore is not affected.
In ltree, when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} (Nikita Glukhov)
FOLIO doesn't use ltree: https://github.com/search?q=org%3Afolio-org+ltree&type=code
Fix pageinspect's bt_metap() to return more appropriate data types that are less likely to overflow (Peter Geoghegan)
FOLIO doesn't use bt_metap: https://github.com/search?q=org%3Afolio-org+bt_metap&type=code

14

https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.14.4

User-defined objects that reference certain built-in array functions along with their argument types must be recreated (Tom Lane)
Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() used to take anyarray arguments but now take anycompatiblearray. Therefore, user-defined objects like aggregates and operators that reference those array function signatures must be dropped before upgrading, and recreated once the upgrade completes.
FOLIO uses the array_agg function: https://github.com/search?q=org%3Afolio-org+array_position&type=code
However, this is always a plain usage within an SELECT statement without signature reference and not a user-defined object like an aggregate or an operator that references the signature.
FOLIO doesn't use the other array functions:
- https://github.com/search?q=org%3Afolio-org+array_append&type=code
- https://github.com/search?q=org%3Afolio-org+array_prepend&type=code
- https://github.com/search?q=org%3Afolio-org+array_cat&type=code
- https://github.com/search?q=org%3Afolio-org+array_positions&type=code
- https://github.com/search?q=org%3Afolio-org+array_remove&type=code
- https://github.com/search?q=org%3Afolio-org+array_replace&type=code
- https://github.com/search?q=org%3Afolio-org+width_bucket&type=code
Remove deprecated containment operators @ and ~ for built-in geometric data types and contrib modules cube, hstore, intarray, and seg (Justin Pryzby)
The more consistently named <@ and @> have been recommended for many years.
FOLIO doesn't use geometric data types in the database. Even if it was the non-deprecated operators would be used.
FOLIO doesn't use the modules:
https://github.com/search?q=org%3Afolio-org+cube&type=code
https://github.com/search?q=org%3Afolio-org+hstore&type=code
https://github.com/search?q=org%3Afolio-org%20intarray&type=code
https://github.com/search?q=org%3Afolio-org+%22create+extension%22+seg&type=code
Fix to_tsquery() and websearch_to_tsquery() to properly parse query text containing discarded tokens (Alexander Korotkov)
Certain discarded tokens, like underscore, caused the output of these functions to produce incorrect tsquery output, e.g., both websearch_to_tsquery('"pg_class pg"') and to_tsquery('pg_class <-> pg') used to output ( 'pg' & 'class' ) <-> 'pg', but now both output 'pg' <-> 'class' <-> 'pg'.
FOLIO doesn't use websearch_to_tsquery: https://github.com/search?q=org%3Afolio-org+websearch_to_tsquery&type=code
to_tsquery usages: https://github.com/search?q=org%3Afolio-org+to_tsquery&type=code
FOLIO doesn't rely on this bug; the fix in PostgreSQL fixes the issue in FOLIO:
to_tsquery is used in raml-module-builder but doesn't rely on the bug.
to_tsquery is used in mod-notes and mod-permissions test code but doesn't reply on the bug.
to_tsquery is used in archived repositories.
Fix websearch_to_tsquery() to properly parse multiple adjacent discarded tokens in quotes (Alexander Korotkov)
Previously, quoted text that contained multiple adjacent discarded tokens was treated as multiple tokens, causing incorrect tsquery output, e.g., websearch_to_tsquery('"aaa: bbb"') used to output 'aaa' <2> 'bbb', but now outputs 'aaa' <-> 'bbb'.
FOLIO doesn't use websearch_to_tsquery: https://github.com/search?q=org%3Afolio-org+websearch_to_tsquery&type=code
Change EXTRACT() to return type numeric instead of float8 (Peter Eisentraut)
This avoids loss-of-precision issues in some usages. The old behavior can still be obtained by using the old underlying function date_part().
Also, EXTRACT(date) now throws an error for units that are not part of the date data type.
The GitHub search yields too many false positivies.
() Better precision and throwing an error in case of a syntax error is very unlikely to affect FOLIO.
Change var_samp() and stddev_samp() with numeric parameters to return NULL when the input is a single NaN value (Tom Lane)
Previously NaN was returned.
FOLIO neither uses var_samp nor stddev_samp: https://github.com/search?q=org%3Afolio-org+var_samp&type=code , https://github.com/search?q=org%3Afolio-org+stddev_samp&type=code
Return false for has_column_privilege() checks on non-existent or dropped columns when using attribute numbers (Joe Conway)
Previously such attribute numbers returned an invalid-column error.
FOLIO doesn't use has_column_privilege: https://github.com/search?q=org%3Afolio-org+has_column_privilege&type=code
Fix handling of infinite window function ranges (Tom Lane)
Previously window frame clauses like 'inf' PRECEDING AND 'inf' FOLLOWING returned incorrect results.
FOLIO neither uses 'inf' FOLLOWING nor 'inf' PRECEDING: https://github.com/search?q=org%3Afolio-org+%22inf+following%22&type=code , https://github.com/search?q=org%3Afolio-org+%22inf+preceding%22&type=code
Remove factorial operators ! and !!, as well as function numeric_fac() (Mark Dilger)
The factorial() function is still supported.
() It's very unlikely that FOLIO uses a factorial computation in the database.
Disallow factorial() of negative numbers (Peter Eisentraut)
Previously such cases returned 1.
FOLIO doesn't use factorial: https://github.com/search?q=org%3Afolio-org+%22factorial%22&type=code
Remove support for postfix (right-unary) operators (Mark Dilger)
pg_dump and pg_upgrade will warn if postfix operators are being dumped.
FOLIO doesn't use CREATE OPERATOR: https://github.com/search?q=org%3Afolio-org+%22CREATE+OPERATOR%22&type=code
Allow \D and \W shorthands to match newlines in regular expression newline-sensitive mode (Tom Lane)
Previously they did not match newlines in this mode, but that disagrees with the behavior of other common regular expression engines. [^[:digit:]] or [^[:word:]] can be used to get the old behavior.
\D and \W are not used in the database, only in other languages like javascript, helm charts, bash, perl: https://github.com/search?q=org%3Afolio-org+%22\D%22&type=code , https://github.com/search?q=org%3Afolio-org+%22\W%22&type=code
Disregard constraints when matching regular expression back-references (Tom Lane)
For example, in (^\d+).*\1, the ^ constraint should be applied at the start of the string, but not when matching \1.
() No GitHub search is possible.
Is't very unlikely that FOLIO is affected because disregarding the constraints is what the developers have intended, or the developers would have moved the constraint out of the braces, for example ^(\d+).*\1.
Disallow \w as a range start or end in regular expression character classes (Tom Lane)
This previously was allowed but produced unexpected results.
FOLIO doesn't use \w as a character class range start or end in the database, only in javascript: https://github.com/search?q=org%3Afolio-org+%22\w%22&type=code
Require custom server parameter names to use only characters that are valid in unquoted SQL identifiers (Tom Lane)
FOLIO is not affected, this only affects database administrators.
Change the default of the password_encryption server parameter to scram-sha-256 (Peter Eisentraut)
Previously it was md5. All new passwords will be stored as SHA256 unless this server setting is changed or the password is specified in MD5 format. Also, the legacy (and undocumented) Boolean-like values which were previously synonyms for md5 are no longer accepted.
FOLIO is not affected, this only affects database administrators and has been noted in the FOLIO installation docs since June 2022: https://docs.folio.org/docs/getting-started/installation/#postgresql
Remove server parameter vacuum_cleanup_index_scale_factor (Peter Geoghegan)
This setting was ignored starting in PostgreSQL version 13.3.
FOLIO is not affected, this only affects database administrators.
Remove server parameter operator_precedence_warning (Tom Lane)
This setting was used for warning applications about PostgreSQL 9.5 changes.
FOLIO is not affected, this only affects database administrators.
Overhaul the specification of clientcert in pg_hba.conf (Kyotaro Horiguchi)
Values 1/0/no-verify are no longer supported; only the strings verify-ca and verify-full can be used. Also, disallow verify-ca if cert authentication is enabled since cert requires verify-full checking.
FOLIO is not affected, this only affects database administrators.
Remove support for SSL compression (Daniel Gustafsson, Michael Paquier)
This was already disabled by default in previous PostgreSQL releases, and most modern OpenSSL and TLS versions no longer support it.
FOLIO is not affected, this only affects database administrators.
Remove server and libpq support for the version 2 wire protocol (Heikki Linnakangas)
This was last used as the default in PostgreSQL 7.3 (released in 2002).
FOLIO is not affected because it uses PostgreSQL libraries (Spring, Vert.x, JDBC) that implement the version 3 protocol.
Disallow single-quoting of the language name in the CREATE/DROP LANGUAGE command (Peter Eisentraut)
FOLIO uses neither CREATE LANGUAGE nor DROP LANGUAGE: https://github.com/search?q=org%3Afolio-org+%22CREATE+LANGUAGE%22&type=code , https://github.com/search?q=org%3Afolio-org+%22DROP+LANGUAGE%22&type=code
Remove the composite types that were formerly created for sequences and toast tables (Tom Lane)
() It's not possible to create a search for this.
It's very unlikely that this strange syntax has been used by a FOLIO developer and has passed code review.
If used it throws a database error so it should get caught in unit tests.
Process doubled quote marks in ecpg SQL command strings correctly (Tom Lane)
Previously 'abc''def' was passed to the server as 'abc'def', and "abc""def" was passed as "abc"def", causing syntax errors.
FOLIO doesn't use embedded SQL in C.
Prevent the containment operators (<@ and @>) for intarray from using GiST indexes (Tom Lane)
Previously a full GiST index scan was required, so just avoid that and scan the heap, which is faster. Indexes created for this purpose should be removed.
FOLIO doesn't use intarray: https://github.com/search?q=org%3Afolio-org+intarray&type=code
Remove contrib program pg_standby (Justin Pryzby)
FOLIO doesn't use pg_standby, this is a database administrator tool: https://github.com/search?q=org%3Afolio-org%20pg_standby&type=code
Prevent tablefunc's function normal_rand() from accepting negative values (Ashutosh Bapat)
Negative values produced undesirable results.
FOLIO doesn't use normal_rand: https://github.com/search?q=org%3Afolio-org+normal_rand&type=code

15

https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.4

Remove PUBLIC creation permission on the public schema (Noah Misch)
The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.
For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

FOLIO has removed the permission since Iris (R1 2021) release and therefore already uses this new default: https://github.com/folio-org/raml-module-builder/commit/dfae85496591ce080cdb0544a156e1dd9cc8e13e
Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)
This allows each database's owner to have ownership privileges on the public schema within their database. Previously it was owned by the bootstrap superuser, so that non-superuser database owners could not do anything with it.
This change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing ownership specification.

FOLIO uses public schema for extensions only and therefore is not affected by this change.
Remove long-deprecated exclusive backup mode (David Steele, Nathan Bossart)
If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.

FOLIO doesn't use pg_basebackup in exclusive backup mode, and doesn't use the removed methods: https://github.com/search?q=org%3Afolio-org%20pg_start_backup&type=code , https://github.com/search?q=org%3Afolio-org%20pg_stop_backup&type=code , https://github.com/search?q=org%3Afolio-org%20pg_backup_start_time&type=code , https://github.com/search?q=org%3Afolio-org%20pg_is_in_backup&type=code
Increase hash_mem_multiplier default to 2.0 (Peter Geoghegan)
This allows query hash operations to use more work_mem memory than other operations.

FOLIO doesn't rely on the old default and doesn't use the variable: https://github.com/search?q=org%3Afolio-org%20hash_mem_multiplier&type=code
Remove server-side language plpython2u and generic Python language plpythonu (Andres Freund)
Python 2.x is no longer supported. While the original intent of plpythonu was that it could eventually refer to plpython3u, changing it now seems more likely to cause problems than solve them, so it's just been removed.

FOLIO doesn't use the removed languages: https://github.com/search?q=org%3Afolio-org%20plpython2u&type=code , https://github.com/search?q=org%3Afolio-org%20plpythonu&type=code
Generate an error if array_to_tsvector() is passed an empty-string array element (Jean-Christophe Arnu)
This is prohibited because lexemes should never be empty. Users of previous Postgres releases should verify that no empty lexemes are stored because they can lead to dump/restore failures and inconsistent results.

FOLIO doesn't use the method: https://github.com/search?q=org%3Afolio-org%20array_to_tsvector&type=code
Generate an error when chr() is supplied with a negative argument (Peter Eisentraut)

FOLIO uses CHR(31) only that is not affected: https://github.com/search?q=org%3Afolio-org+%22chr%28%22&type=code
Prevent CREATE OR REPLACE VIEW from changing the collation of an output column (Tom Lane)

FOLIO uses the default collation of the database and is not affected by the view collations.
Disallow zero-length Unicode identifiers, e.g., U&"" (Peter Eisentraut)
Non-Unicode zero-length identifiers were already disallowed.

FOLIO doesn't use this identifier: https://github.com/search?q=org%3Afolio-org+%22U%26%22&type=code
Prevent numeric literals from having non-numeric trailing characters (Peter Eisentraut)
Previously, query text like 123abc would be interpreted as 123 followed by a separate token abc.

FOLIO is affected. One example: https://github.com/folio-org/mod-finance-storage/pull/370/files
Adjust JSON numeric literal processing to match the SQL/JSON-standard (Peter Eisentraut)
This accepts numeric formats like .1 and 1., and disallows trailing junk after numeric literals, like 1.type().

() No easy search possible to investigate whether FOLIO is affected. However, it's verly unlikely that FOLIO relies on trailing junk after numeric literals in JSON.
When interval input provides a fractional value for a unit greater than months, round to the nearest month (Bruce Momjian)
For example, convert 1.99 years to 2 years, not 1 year 11 months as before.

No easy search possible to investigate whether FOLIO uses fractional year intervals and is affected.
Improve consistency of interval parsing with trailing periods (Tom Lane)
Numbers with trailing periods were rejected on some platforms.

() No easy search possible to investigate whether FOLIO is affected. However, it's very unlikely that FOLIO runtime code depends on trailing periods being rejected.
Mark the interval output function as stable, not immutable, since it depends on IntervalStyle (Tom Lane)
This will, for example, cause creation of indexes relying on the text output of interval values to fail.

FOLIO doesn't use IntervalStyle: https://github.com/search?q=org%3Afolio-org%20IntervalStyle&type=code
Detect integer overflow in interval justification functions (Joe Koshakow)
The affected functions are justify_interval(), justify_hours(), and justify_days().

FOLIO doesn't use the functions: https://github.com/search?q=org%3Afolio-org+justify_interval&type=code , https://github.com/search?q=org%3Afolio-org+justify_hours&type=code , https://github.com/search?q=org%3Afolio-org+justify_day&type=code
Change the I/O format of type "char" for non-ASCII characters (Tom Lane)
Bytes with the high bit set are now output as a backslash and three octal digits, to avoid encoding issues.

This output is still a valid representation and very unlikely to cause any issues in FOLIO.
Remove the default ADMIN OPTION privilege a login role has on its own role membership (Robert Haas)
Previously, a login role could add/remove members of its own role, even without ADMIN OPTION privilege.

FOLIO uses ADMIN OPTION privileges where needed.
Allow logical replication to run as the owner of the subscription (Mark Dilger)
Because row-level security policies are not checked, only superusers, roles with bypassrls, and table owners can replicate into tables with row-level security policies.

Replication is a sysop task, FOLIO is not affected, and allowing more roles cannot break an installation.
Prevent UPDATE and DELETE logical replication operations on tables where the subscription owner does not have SELECT permission on the table (Jeff Davis)
UPDATE and DELETE commands typically involve reading the table as well, so require the subscription owner to have table SELECT permission.

Replication is a sysop task, FOLIO is not affected. Sysops can easily add the SELECT permission if needed.
When EXPLAIN references the session's temporary object schema, refer to it as pg_temp (Amul Sul)
Previously the actual schema name was reported, leading to inconsistencies across sessions.

FOLIO doesn't use EXPLAIN with the session's temporary object schema: https://github.com/search?q=org%3Afolio-org+explain&type=code
Fix pg_statio_all_tables to sum values for the rare case of TOAST tables with multiple indexes (Andrei Zubkov)
Previously such cases would show one row for each index.

FOLIO doesn't use pg_statio_all_tables: https://github.com/search?q=org%3Afolio-org%20pg_statio_all_tables&type=code
Disallow setting custom options that match the name of an installed extension, but are not one of the extension's declared variables (Florin Irion, Tom Lane)
This change causes any such pre-existing variables to be deleted during extension load, and then prevents new ones from being created later in the session. The intent is to prevent confusion about whether a variable is associated with an extension or not.

No easy search is possible to find out whether FOLIO uses confusing database variables. However, this change will fail unit tests that use such variables.
Remove obsolete server variable stats_temp_directory (Andres Freund, Kyotaro Horiguchi)

FOLIO doesn't use the variable: https://github.com/search?q=org%3Afolio-org+stats_temp_directory&type=code
Improve the algorithm used to compute random() (Fabien Coelho)
This will cause random()'s results to differ from what was emitted by prior versions, even for the same seed value.

FOLIO uses PostgreSQL's random() but all uses cases don't require reproducibility: https://github.com/search?q=org%3Afolio-org+random()&type=code
libpq's PQsendQuery() function is no longer supported in pipeline mode (Álvaro Herrera)
Applications that are using that combination will need to be modified to use PQsendQueryParams() instead.

FOLIO doesn't use PQsendQuery: https://github.com/search?q=org%3Afolio-org+PQsendQuery&type=code
On non-Windows platforms, consult the HOME environment variable to find the user's home directory (Anders Kaseorg)
If HOME is empty or unset, fall back to the previous method of checking the <pwd.h> database. This change affects libpq (for example, while looking up ~/.pgpass) as well as various client application programs.

FOLIO modules get the PostgreSQL configuration from environment variables. Only sysop tasks that use client applications might be affected.
Remove pg_dump's --no-synchronized-snapshots option (Tom Lane)
All still-supported server versions support synchronized snapshots, so there's no longer a need for this option.

FOLIO doesn't use the option: https://github.com/search?q=org%3Afolio-org+no-synchronized-snapshots&type=code
After an error is detected in psql's --single-transaction mode, change the final COMMIT command to ROLLBACK only if ON_ERROR_STOP is set (Michael Paquier)

FOLIO doesn't use the option: https://github.com/search?q=org%3Afolio-org+single-transaction&type=code
Avoid unnecessary casting of constants in queries sent by postgres_fdw (Dian Fay)
When column types are intentionally different between local and remote databases, such casts could cause errors.

Configuring a remote database is a sysop task. FOLIO doesn't use this feature: https://github.com/search?q=org%3Afolio-org+postgres_fdw&type=code
Remove xml2's xml_is_well_formed() function (Tom Lane)
This function has been implemented in the core backend since Postgres 9.1.

FOLIO doesn't use the function: https://github.com/search?q=org%3Afolio-org+xml_is_well_formed&type=code
Allow custom scan providers to indicate if they support projections (Sven Klemm)
The default is now that custom scan providers are assumed to not support projections; those that do will need to be updated for this release.

FOLIO doesn't use this feature: https://github.com/search?q=org%3Afolio-org+set_rel_pathlist_hook_type&type=code

16

https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-MIGRATION

Change assignment rules for PL/pgSQL bound cursor variables (Tom Lane)
Previously, the string value of such variables was set to match the variable name during cursor assignment; now it will be assigned during OPEN, and will not match the variable name. To restore the previous behavior, assign the desired portal name to the cursor variable before OPEN.
FOLIO doesn't use this variable assignment. Only mod-finance-storage and mod-marccat use OPEN with an SQL cursor, but they don't use the variable assignment: https://github.com/search?q=org%3Afolio-org+%22open+%22+cursor&type=code&p=3
Disallow NULLS NOT DISTINCT indexes for primary keys (Daniel Gustafsson)
FOLIO doesn't use NULLS NOT DISTINCT: https://github.com/search?q=org%3Afolio-org+%22NULLS+NOT+DISTINCT%22&type=code
Change REINDEX DATABASE and reindexdb to not process indexes on system catalogs (Simon Riggs)
Processing such indexes is still possible using REINDEX SYSTEM and reindexdb --system.
FOLIO doesn't use REINDEX DATABASE: https://github.com/search?q=org%3Afolio-org+%22REINDEX+DATABASE%22&type=code
Tighten GENERATED expression restrictions on inherited and partitioned tables (Amit Langote, Tom Lane)
Columns of parent/partitioned and child/partition tables must all have the same generation status, though now the actual generation expressions can be different.
No easy search is possible to find out whether FOLIO uses generated columns with inherited or partitioned tables. However, if affected it will fail early when creating tables.
Remove pg_walinspect functions pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() (Bharath Rupireddy)
FOLIO doesn't use these functions: https://github.com/search?q=org%3Afolio-org+pg_get_wal_records_info_till_end_of_wal&type=code , https://github.com/search?q=org%3Afolio-org+pg_get_wal_stats_till_end_of_wal&type=code
Rename server variable force_parallel_mode to debug_parallel_query (David Rowley)
FOLIO doesn't use the the variable: https://github.com/search?q=org%3Afolio-org+force_parallel_mode&type=code
Remove the ability to create views manually with ON SELECT rules (Tom Lane)
FOLIO doesn't use CREATE VIEW with ON SELECT: https://github.com/search?q=org%3Afolio-org+%22ON+SELECT%22+CREATE+VIEW&type=code
Remove the server variable vacuum_defer_cleanup_age (Andres Freund)
This has been unnecessary since hot_standby_feedback and replication slots were added.
FOLIO doesn't use that variable: https://github.com/search?q=org%3Afolio-org+vacuum_defer_cleanup_age&type=code
Remove server variable promote_trigger_file (Simon Riggs)
This was used to promote a standby to primary, but is now more easily accomplished with pg_ctl promote or pg_promote().
FOLIO doesn't use that variable: https://github.com/search?q=org%3Afolio-org+promote_trigger_file&type=code
Remove read-only server variables lc_collate and lc_ctype (Peter Eisentraut)
Collations and locales can vary between databases so having them as read-only server variables was unhelpful.
FOLIO doesn't use these server variables: https://github.com/search?q=org%3Afolio-org+lc_collate&type=code , https://github.com/search?q=org%3Afolio-org+lc_ctype&type=code
Role inheritance now controls the default inheritance status of member roles added during GRANT (Robert Haas)
The role's default inheritance behavior can be overridden with the new GRANT ... WITH INHERIT clause. This allows inheritance of some roles and not others because the members' inheritance status is set at GRANT time. Previously the inheritance status of member roles was controlled only by the role's inheritance status, and changes to a role's inheritance status affected all previous and future member roles.
No easy search is possible to find out whether FOLIO relies on the inheritance when using GRANT. However, if affected it will fail early when creating database roles.
Restrict the privileges of CREATEROLE and its ability to modify other roles (Robert Haas)
Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission. For example, they can now change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.
FOLIO uses CREATEROLE for testing only, not for production code: https://github.com/search?q=org%3Afolio-org+CREATEROLE&type=code
Remove symbolic links for the postmaster binary (Peter Eisentraut)
FOLIO doesn't use the postmaster binary, it connects to the database via TCP or local Unix-domain socket file.

Module tests

Module tests (mvn verify) using DR-000037 - TESTCONTAINERS_POSTGRES_IMAGE:

module with link to test results	postgres:13-alpine	postgres:14-alpine	postgres:15-alpine	postgres:16-alpine
mod-audit	13 OK	14 OK	15 OK	16 OK
mod-bulk-operations	13 OK	14 OK	15 OK	16 OK
mod-calendar	13 OK	14 OK	15 OK	16 OK
mod-circulation-storage	13 OK	14 OK	15 OK	16 OK
mod-consortia	13 OK	14 OK	15 OK	16 OK
mod-data-export	13 OK	14 OK	15 OK	16 OK
mod-data-export-spring	13 OK	14 OK	15 OK	16 OK
mod-data-export-worker	13 OK	14 OK	15 OK	16 OK
mod-data-import	13 OK	14 OK	15 OK	16 OK
mod-dcb	13 OK	14 OK	15 OK	16 OK
mod-di-converter-storage	13 OK	14 OK	15 OK	16 OK
mod-entities-links no regressions, 129 succeeding tests	13 OK	14 OK	15 OK	16 OK
mod-erm-usage	13 OK	14 OK	15 OK	16 OK
mod-erm-usage-harvester	13 OK	14 OK	15 OK	16 OK
mod-eusage-reports	13 OK	14 OK	15 OK	16 OK
mod-feesfines	13 OK	14 OK	15 OK	16 OK
mod-finance-storage	13 OK	14 OK	15 OK	16 OK
mod-finc-config	13 OK	14 OK	15 OK	16 OK
mod-inn-reach	13 OK	14 OK	15 OK	16 OK
mod-inventory-storage	13 OK	14 OK	15 OK	16 OK
mod-invoice	13 OK	14 OK	15 OK	16 OK
mod-lists	13 OK	14 OK	15 OK	16 OK
mod-login	13 OK	14 OK	15 OK	16 OK
mod-oai-pmh	13 OK	14 OK	15 OK	16 OK
mod-orders-storage	13 OK	14 OK	15 OK	16 OK
mod-patron-blocks	13 OK	14 OK	15 OK	16 OK
mod-pubsub	13 OK	14 OK	15 OK	16 OK
mod-quick-marc	13 OK	14 OK	15 OK	16 OK
mod-remote-storage	13 OK	14 OK	15 OK	16 OK
mod-reservoir	13 OK	14 OK	15 OK	16 OK
mod-search	13 OK	14 OK	15 OK	16 OK
mod-source-record-manager	13 OK	14 OK	15 OK	16 OK
mod-source-record-storage no regressions, 506 succeeding tests	13 OK	14 OK	15 OK	16 OK
mod-tags	13 OK	14 OK	15 OK	16 OK
mod-users	13 OK	14 OK	15 OK	16 OK
okapi	13 OK	14 OK	15 OK	16 OK

The modules for this list have been selected by highest lines of code as reported by Sonar.

How can I see that the build actually uses version 13, 14,15 or 16? In the build report click on the "Run mvn --batch-mode verify" step, then go to the "search logs" slot and enter "postgres:".

Why has the issue with 14 in one module removed from the table? It turned out that it is a sporadic test failure unrelated to the PostgreSQL version, it happens in versions 12, 13, 14, 15 and 16.

What does "no regressions, 129 succeeding tests" mean? This means that the module has 129 succeeding tests and one or more failing tests with PostgreSQL 12 and this doesn't change when using PostgreSQL 13, 14, 15 or 16.

Browser not supported