icu-multilib.html
application/xhtml+xml
Filename: icu-multilib.html
Type: application/xhtml+xml
Part: 0
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.19. icu_multilib</title><link rel="stylesheet" type="text/css" href="https://www.postgresql.org/media/css/docs-complete.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="hstore.html" title="F.18. hstore" /><link rel="next" href="intagg.html" title="F.20. intagg" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.19. icu_multilib</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16devel Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="intagg.html" title="F.20. intagg">Next</a></td></tr></table><hr /></div><div class="sect1" id="ICU-MULTILIB"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.19. icu_multilib</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="icu-multilib.html#ICU-MULTILIB-VERSION-STABILITY">F.19.1. ICU Version Stability</a></span></dt><dt><span class="sect2"><a href="icu-multilib.html#ICU-MULTI-VERSION">F.19.2. Multiple ICU Version Support</a></span></dt><dt><span class="sect2"><a href="icu-multilib.html#ICU-MULTILIB-CONFIGURATION-PARAMETERS">F.19.3. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="icu-multilib.html#id-1.11.7.28.10">F.19.4. Functions</a></span></dt><dt><span class="sect2"><a href="icu-multilib.html#ICU-MULTILIB-EXAMPLES">F.19.5. Examples</a></span></dt><dt><span class="sect2"><a href="icu-multilib.html#ICU-MULTILIB-AUTHOR">F.19.6. Authors</a></span></dt></dl></div><a id="id-1.11.7.28.2" class="indexterm"></a><p>
The <code class="filename">icu_multilib</code> module provides control over the
version (or versions) of the ICU provider library used by
<span class="productname">PostgreSQL</span>, which can be different from the
version of ICU with which it was built.
</p><p>
Collations are a product of natural language, and natural language evolves
over time; but <span class="productname">PostgreSQL</span> depends on stable
ordering for structures such as indexes. Newer versions of ICU update the
provided collators to adapt to changes in natural language, so it's
important to control when and how those new versions of ICU are used to
prevent problems such as index corruption.
</p><p>
This module assumes that the necessary versions of ICU are already
available, such as through the operating system's package manager; and
already properly installed in a single location accessible to
<span class="productname">PostgreSQL</span>. The configration variable
<code class="literal">icu_multilib.library_path</code> should be set to the location
where these ICU library versions are installed.
</p><p>
<code class="filename">icu_multilib</code> must be loaded via
<code class="literal">shared_preload_libraries</code>.
<code class="filename">icu_multilib</code> ignores any ICU library with a major
version greater than that with which <span class="productname">PostgreSQL</span>
was built.
</p><div class="sect2" id="ICU-MULTILIB-VERSION-STABILITY"><div class="titlepage"><div><div><h3 class="title">F.19.1. ICU Version Stability</h3></div></div></div><p>
The simplest way to use <code class="filename">icu_multilib</code> is to force ICU
version stability. Version stability means that
<span class="productname">PostgreSQL</span> will use the same version of ICU for
all collations, even if the operating system or
<span class="productname">PostgreSQL</span> itself is upgraded or moved. The
stable version may or may not be the same as that with which
<span class="productname">PostgreSQL</span> was built.
</p><p>
Version stability may be based on both the major and minor version of ICU,
such as <code class="literal">68.2</code>; or on the major version alone, such as
<code class="literal">68</code>. Specifying both the major and minor versions more
closely controls exactly which ICU library is loaded, but applying minor
version upgrades of the ICU library may interfere with normal
operation. Specifying the major version alone makes applying ICU minor
version updates simpler, but carries a risk of subtle differences in
collation order if the library is updated.
</p><p>
To configure for version stability, first make sure that
<code class="varname">icu_multilib.library_path</code> is set correctly, which you
can observe by executing the
<code class="literal">icu_multilib.library_versions()</code> function. Then set
<code class="varname">icu_multilib.default_icu_version</code> to the stable version,
and set <code class="varname">icu_multilib.search_by_collator_version</code> to
<code class="literal">false</code>. These configuration variables should be set in
<code class="filename">postgresql.conf</code> to ensure that the stable version of
ICU is used consistently.
</p><div class="note"><h3 class="title">Note</h3><p>
Ensure that the stable version of the ICU library is always available,
including on all replicas and through operating system and
<span class="productname">PostgreSQL</span> upgrades.
</p><p>
Similarly, keep the settings in <code class="filename">postgresql.conf</code>, and
keep them consistent across replicas and upgrades.
</p></div><p>
For best results, stabilize the ICU version immediately after
<code class="command">initdb</code> is run, to ensure that initial collations are
loaded from the stabilized version of the ICU library. To do this, use the
option <a class="xref" href="app-initdb.html#APP-INITDB-OPTION-NO-IMPORT-COLLATIONS"><code class="option">--no-import-collations</code></a> for
<code class="command">initdb</code>, import the collations manually, and then refresh
the versions for the default collations. For instance:
</p><pre class="programlisting">
$ initdb --no-import-collations -D data --locale-provider=icu --icu-locale="en_US" --locale="en_US.UTF-8"
$ cat - >> data/postgresql.conf
shared_preload_libraries = 'icu_multilib'
icu_multilib.library_path = '/path/to/icu/lib'
icu_multilib.default_icu_version = '65.1'
icu_multilib.search_by_collator_version = false
icu_multilib.include_builtin = false
^D
$ pg_ctl -D data -l logfile start
$ psql template1
WARNING: icu_multilib: collator version mismatch detected for locale "en_US"
DETAIL: ICU 65.1 provides collator version "153.97" for locale "en_US"; expected version "153.112".
WARNING: icu_multilib: collator version mismatch detected for locale "en_US"
DETAIL: ICU 65.1 provides collator version "153.97" for locale "en_US"; expected version "153.112".
WARNING: database "template1" has a collation version mismatch
DETAIL: The database was created using collation version 153.112, but the operating system provides version 153.97.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
=# ALTER DATABASE template1 REFRESH COLLATION VERSION;
NOTICE: changing version from 153.112 to 153.97
ALTER DATABASE
=# SELECT pg_import_system_collations('pg_catalog');
pg_import_system_collations
-----------------------------
792
(1 row)
=# \q
$ psql postgres
WARNING: icu_multilib: collator version mismatch detected for locale "en_US"
DETAIL: ICU 65.1 provides collator version "153.97" for locale "en_US"; expected version "153.112".
WARNING: icu_multilib: collator version mismatch detected for locale "en_US"
DETAIL: ICU 65.1 provides collator version "153.97" for locale "en_US"; expected version "153.112".
WARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 153.112, but the operating system provides version 153.97.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE: changing version from 153.112 to 153.97
ALTER DATABASE
=# SELECT pg_import_system_collations('pg_catalog');
pg_import_system_collations
-----------------------------
792
(1 row)
=# \q
$
</pre><p>
The result will be a system based on ICU version <code class="literal">65.1</code>, and
<span class="productname">PostgreSQL</span> will use that version of ICU for all
collations that use the ICU provider.
</p></div><div class="sect2" id="ICU-MULTI-VERSION"><div class="titlepage"><div><div><h3 class="title">F.19.2. Multiple ICU Version Support</h3></div></div></div><p>
Support for multiple ICU library versions in the same database allows the
system to adapt to changes in natural language over time. As these changes
are introduced in new versions of ICU, those new versions can be brought
into <span class="productname">PostgreSQL</span> incrementally without
interfering with existing data (or structures like indexes).
</p><div class="sect3" id="id-1.11.7.28.8.3"><div class="titlepage"><div><div><h4 class="title">F.19.2.1. Search by Collator Version</h4></div></div></div><p>
Collators provided by ICU also have a version which is distinct from the
version of ICU. For instance, in ICU version <code class="literal">70.1</code>, the
collator for the <code class="literal">en_US</code> locale has version
<code class="literal">153.112</code>. A collator for a given locale may or may not
be assigned a new version when the ICU library version is updated.
</p><p>
When a collation is created, <span class="productname">PostgreSQL</span> obtains
the version of the collator from the ICU library and records it in the
catalog. This recorded version can be updated using <code class="literal">ALTER
COLLATION ... REFRESH VERSION</code>; or <code class="literal">ALTER DATABASE
... REFRESH COLLATION VERSION</code> if it's the database's default
collation. These <code class="literal">ALTER</code> commands obtain the new collator
version from the ICU library identified by
<code class="varname">icu_multilib.default_icu_version</code> if set; otherwise they
obtain the new collator version from the built-in ICU library.
</p><p>
Set <code class="varname">icu_multilib.search_by_collator_version</code> to
<code class="literal">true</code> to enable <code class="filename">icu_multilib</code> to
search (in descending order of ICU major version) among the
available libraries to find the first one that provides a collator with a
version that matches the one recorded in the catalog.
</p><p>
For example, ICU versions <code class="literal">62.2</code> and
<code class="literal">63.2</code> both provide collator version
<code class="literal">153.88</code> for the locale <code class="literal">en_US</code>; while
ICU version <code class="literal">64.2</code> provides collator version
<code class="literal">153.97</code> for the same locale. Searching for an ICU
library for a collation with a recorded version of
<code class="literal">153.88</code> will find the ICU library with version
<code class="literal">63.2</code>, because that's the ICU library with the highest
major version (<code class="literal">63</code>) that provides collator version
<code class="literal">153.88</code>. The function
<code class="function">icu_multilib.collator_version_search</code> simulates this
search for the given locale and version:
</p><pre class="programlisting">
SELECT * FROM icu_multilib.collator_version_search('en_US', '153.88');
icu_version | uca_version | collator_version
-------------+-------------+------------------
63.2 | 11.0 | 153.88
(1 row)
</pre><div class="note"><h3 class="title">Note</h3><p>
Searching by collator version relies on the assumption that collators
with the same version behave identically regardless of which ICU library
provides it. The administrator must weigh the risks of this assumption
against the convenience when managing multiple major versions of ICU.
</p></div></div></div><div class="sect2" id="ICU-MULTILIB-CONFIGURATION-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">F.19.3. Configuration Parameters</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">
<code class="varname">icu_multilib.library_path</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
The filesystem path in which to search for ICU libraries. Must be set
for <code class="filename">icu_multilib</code> to be useful. Multiple major
versions of ICU may be present; but for each major version, only a
single minor version can be present.
</p></dd><dt><span class="term">
<code class="varname">icu_multilib.version_mismatch_log_level</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
A version mismatch happens when the version of a collator provided by
the ICU library is different than then collator version recorded when
the collation was created (or last refreshed).
</p><p>
When a version mismatch is detected, a log message is emitted at this
level. <code class="literal">WARNING</code> is the default. If the level is set to
<code class="literal">ERROR</code>, it may interfere with normal operation.
</p></dd><dt><span class="term">
<code class="varname">icu_multilib.library_search_log_level</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
<code class="filename">icu_multilib</code> emits messages at this level while
determining which ICU library to choose. The default is
<code class="literal">DEBUG1</code>. This setting is useful to diagnose problems
when a suitable ICU library is not found.
</p></dd><dt><span class="term">
<code class="varname">icu_multilib.default_icu_version</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Identifies the version of the ICU library to choose; or, if <code class="varname">icu_multilib.search_by_collator_version</code> is <code class="literal">true</code>, the ICU library to choose if no identical match to the collator version is found. The default is the empty string, meaning to use the built-in ICU library.
</p><p>
Valid values are either of the form <code class="literal">major.minor</code> such
as <code class="literal">70.1</code>; or a major version number alone, such as
<code class="literal">70</code>.
</p></dd><dt><span class="term">
<code class="varname">icu_multilib.include_builtin</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
Determines whether the built-in version of ICU is included when
searching for a suitable ICU library. The default is
<code class="literal">true</code>.
</p><p>
Even if set to <code class="literal">false</code>, the built-in ICU library may be
used if no other suitable ICU library is identified and
<code class="varname">icu_multilib.default_icu_version</code> is set to the empty
string.
</p></dd><dt><span class="term">
<code class="varname">icu_multilib.search_by_collator_version</code> (<code class="type">string</code>)
<a id="id-1.11.7.28.9.2.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes <code class="filename">icu_multilib</code> to identify the ICU library
with the highest major version that offers the required collator with an
exactly-matching collator version. The default is <code class="literal">false</code>.
</p><p>
When set to <code class="literal">true</code>, <code class="filename">icu_multilib</code>
relies on equal collator versions to produce identical collation order.
</p><p>
If no ICU library is found with an exactly-matching collator version,
<code class="filename">icu_multilib</code> will fall back to the
<code class="varname">icu_multilib.default_icu_version</code>; or if not set, to
the built-in ICU library.
</p></dd></dl></div></div><div class="sect2" id="id-1.11.7.28.10"><div class="titlepage"><div><div><h3 class="title">F.19.4. Functions</h3></div></div></div><div class="table" id="ICU-MULTILIB-FUNCTIONS"><p class="title"><strong>Table F.9. <code class="filename">icu_multilib</code> Functions</strong></p><div class="table-contents"><table class="table" summary="icu_multilib Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">icu_multilib.library_versions</code> ()
→ <code class="returnvalue">setof record</code>
( <em class="parameter"><code>icu_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>unicode_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>cldr_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>libicui18n_name</code></em> <code class="type">text</code>,
<em class="parameter"><code>libicuuc_name</code></em> <code class="type">text</code>)
</p>
<p>
Returns details for each available ICU library found in
<code class="varname">icu_multilib.library_path</code>. Also includes the
built-in ICU library iv <code class="varname">icu_multilib.include_builtin</code>
is <code class="literal">true</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">icu_multilib.library_collators</code> ( <em class="parameter"><code>major_version</code></em> <code class="type">text</code> <code class="literal">DEFAULT</code> <code class="literal">null</code> )
→ <code class="returnvalue">setof record</code>
( <em class="parameter"><code>locale</code></em> <code class="type">text</code>,
<em class="parameter"><code>icu_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>uca_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>collator_version</code></em> <code class="type">text</code> )
</p>
<p>
Returns details for all available collators provided by the ICU library
with the given <em class="parameter"><code>major_version</code></em>; or by the built-in
ICU library if <em class="parameter"><code>major_version</code></em> is
<code class="literal">null</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">icu_multilib.collator_version_search</code> ( <em class="parameter"><code>locale</code></em> <code class="type">text</code>, <em class="parameter"><code>requested_version</code></em> <code class="type">text</code> <code class="literal">DEFAULT</code> <code class="literal">null</code>, <em class="parameter"><code>log_ok</code></em> <code class="type">boolean</code> <code class="literal">DEFAULT</code> <code class="literal">false</code> )
→ <code class="returnvalue">record</code>
( <em class="parameter"><code>icu_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>uca_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>collator_version</code></em> <code class="type">text</code> )
</p>
<p>
Performs a search for the appropriate ICU library given the locale name
and requested collator version, and returns details about the ICU
library.
</p>
<p>
If <em class="parameter"><code>requested_version</code></em> is <code class="literal">null</code>,
it will return the ICU library identified by
<code class="varname">icu_multilib.default_icu_version</code> if set to
<code class="literal">true</code>; otherwise the built-in ICU library.
</p>
<p>
If <em class="parameter"><code>log_ok</code></em> is <code class="literal">true</code>, the search
may emit log messages at the level
<code class="varname">icu_multilib.library_search_log_level</code>, which may be
useful for diagnosing misconfiguration.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">icu_multilib.collator_versions</code> ( <em class="parameter"><code>locale</code></em> <code class="type">text</code> )
→ <code class="returnvalue">setof record</code>
( <em class="parameter"><code>icu_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>uca_version</code></em> <code class="type">text</code>,
<em class="parameter"><code>collator_version</code></em> <code class="type">text</code> )
</p>
<p>
Returns details about available collators for the given
<em class="parameter"><code>locale</code></em> from all available ICU libraries.
</p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="ICU-MULTILIB-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">F.19.5. Examples</h3></div></div></div><pre class="screen">
SELECT icu_version, unicode_version, cldr_version FROM icu_multilib.library_versions() LIMIT 3;
icu_version | unicode_version | cldr_version
-------------+-----------------+--------------
70.1 | 14.0 | 40.0
69.1 | 13.0 | 39.0
68.2 | 13.0 | 38.1
(3 rows)
</pre><pre class="screen">
SELECT * FROM icu_multilib.collator_versions('en_US') WHERE icu_version BETWEEN '60.0' and '65.0';
icu_version | uca_version | collator_version
-------------+-------------+------------------
64.2 | 12.1 | 153.97
63.2 | 11.0 | 153.88
62.2 | 11.0 | 153.88
61.2 | 10.0 | 153.80
60.3 | 10.0 | 153.80
(5 rows)
</pre><pre class="screen">
SELECT * FROM icu_multilib.collator_version_search('en_US', '153.97');
icu_version | uca_version | collator_version
-------------+-------------+------------------
65.1 | 12.1 | 153.97
(1 row)
</pre></div><div class="sect2" id="ICU-MULTILIB-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.19.6. Authors</h3></div></div></div><p>
Jeff Davis <code class="email"><<a class="email" href="mailto:jdavis@postgresql.org">jdavis@postgresql.org</a>></code>, Thomas Munro <code class="email"><<a class="email" href="mailto:tmunro@postgresql.org">tmunro@postgresql.org</a>></code>
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intagg.html" title="F.20. intagg">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.18. hstore </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16devel Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.20. intagg</td></tr></table></div></body></html>