Subject: Pg_upgrade and collation

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 Jun 2016 11:43:11 -0400

The attached patch documents that pg_upgrade requires old/new servers to
use compatibile collation library versions as well.

I would like to apply this to all PG branches.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
new file mode 100644
index b99e546..3957241
*** a/doc/src/sgml/ref/pgupgrade.sgml
--- b/doc/src/sgml/ref/pgupgrade.sgml
***************
*** 61,67 ****
    checking for compatible compile-time settings, including 32/64-bit
    binaries.  It is important that
    any external modules are also binary compatible, though this cannot
!   be checked by <application>pg_upgrade</>.
   </para>
  
    <para>
--- 61,68 ----
    checking for compatible compile-time settings, including 32/64-bit
    binaries.  It is important that
    any external modules are also binary compatible, though this cannot
!   be checked by <application>pg_upgrade</>.  Compatible collation
!   library versions must also be used.
   </para>
  
    <para>

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 17 Jun 2016 17:51:54 -0400

I think it would be useful to indicate what to do if they are not
compatible.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 Jun 2016 18:01:59 -0400

> I think this is way too thin to be helpful:

Well, this is a much larger issue than pg_upgrade, e.g. moving a data
directory from one cluster to another with a different collation library
version could also cause problems, and I don't know that is documented
at all.

If we want to go larger, we have to do this in a more central location.

> I think it would be useful to indicate what to do if they are not

The indexes don't work reliably.  We don't document what happens if
shared objects don't match either, but again, if we want to clarify
this, we need to do it more centrally.  Ideas?

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 Jun 2016 18:09:35 -0400

> If we want to go larger, we have to do this in a more central location.

Frankly, pg_upgrade is, by definition, upgrading on the same server, so
I don't even see how they could have mismatched collation library
versions, but it seemed good to document it.  The larger issue of moving
clusters is a separate issue that needs documentation somewhere else.

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 17 Jun 2016 18:11:58 -0400

Bruce Momjian wrote:
> versions, but it seemed good to document it.

By this argument, the proposed patch seems pointless to me.

> The larger issue of moving clusters is a separate issue that needs
> documentation somewhere else.

Sure.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 20 Jun 2016 11:16:36 -0400

> > documentation somewhere else.

In looking at the docs, it seems it would go in the Backup section
somewhere:

	https://www.postgresql.org/docs/9.6/static/backup.html

Seems it would apply to both of these backup sections:

	24.2. File System Level Backup
	24.3. Continuous Archiving and Point-in-Time Recovery (PITR)

and also here:

	25.2. Log-Shipping Standby Servers

It seems odd to put it in all of these places, but where can we
centrally put it?

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 28 Jun 2016 17:21:51 -0400

> It seems odd to put it in all of these places, but where can we

In looking at the docs, I found that the section "Creating a Database
Cluster", which covers initdb and collations, as the best place to put
this warning.  Patch attached.

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index 4a0e35a..6d39ce7
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*************** postgres$ <userinput>initdb -D /usr/loca
*** 183,188 ****
--- 183,197 ----
     locale setting.  For details see <xref linkend="multibyte">.
    </para>
  
+   <para>
+    Non<literal>C</> and and non-<literal>POSIX</> locales rely on the
+    operating system's collation library for character set ordering.
+    This controls the ordering of keys stored in indexes.  For this reason,
+    a cluster cannot switch to an incompatible collation library version,
+    either through snapshot restore, binary streaming replication, or
+    <application>pg_upgrade</> run.
+   </para>
+ 
    <sect2 id="creating-cluster-mount-points">
     <title>Use of Secondary File Systems</title>

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 28 Jun 2016 14:58:58 -0700

> I think this is way too thin to be helpful:

Unfortunately, the reality is that as things stand, there is no way to
test compatibility on all platforms. Glibc does have a notion of
collation versioning, though [1].

I have long advocated adopting ICU as our defacto standard "collation
provider", primarily so that we can directly control collations and
collation versioning. I think that doing this would solve many
problems. Besides, even SQLite has optional ICU support. PostgreSQL is
the only major database system that I'm aware of that relies on
operating system collations exclusively.

I've avoided committing to work on it because I'm concerned that it
would not be well received.

[1] https://www.gnu.org/software/autoconf/manual/autoconf-2.63/html_node/Special-Shell-Variables.html
-- 
Peter Geoghegan

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 28 Jun 2016 18:20:11 -0400

> collation versioning, though [1].

Yes, the patch text is clearly weasel-words in that we can't explain how
to detect incompatible.

> operating system collations exclusively.

I am hopeful ICU has improved enough since we last researched that
support for it will soon be added.

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 28 Jun 2016 15:43:18 -0700

> support for it will soon be added.

There is a patch available that is not ready to be submitted, and
doesn't have a real advocate, but is at least enough to convince me
that it's very doable. Performance is certainly no impediment to
adopting ICU, even without considering that it effectively
re-introduces abbreviated keys for text when the C collation is not
used.

The best argument for ICU is the evidently lax attitude that the glibc
people have towards the correctness and consistency of their
collations:

https://bugzilla.redhat.com/show_bug.cgi?id=1320356#c3

Here, Carlos O'Donnell, a glic committer, says "Regarding (b), the
collations in glibc may change from build to build depending on
changes in the algorithms or locales. You cannot rely on the collation
stay the same once the process exits (nor can you rely upon it via a
shared memory mapping to another process sorting strings in memory)".
Frankly, we have no excuse for not heeding his warning.

I'm not annoyed at the glibc people for taking this position. There
is, quite simply, a misalignment of incentives. For the glibc people,
the assumption is that any problem with collations leads only to
slight annoyance from end users, as when the GUI produces subtly wrong
ordering. Whereas, for us, any inconsistency is an extremely serious
problem. Here we have the maintainers of glibc telling us that they
feel like it's okay that that can happen at any time. Surely that
isn't good enough.

ICU as a project has every incentive to see things the same way as we
do. The library explicitly decouples collation rule versions from
algorithm versions. All of this is carefully considered, for the
benefit of the numerous major database systems that use ICU.

-- 
Peter Geoghegan

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 28 Jun 2016 18:50:15 -0400

> feel like it's okay that that can happen at any time. Surely that

Uhmm.  Until now I saw all this ICU thing as having fringe benefit on
strange platforms only, but it is seeming more and more like we need to
take it seriously.  I'm not prepared to spend effort on it myself,
though.

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 28 Jun 2016 16:06:49 -0700

> take it seriously.  I'm not prepared to spend effort on it myself,

Let me put it this way: If we lived in a world where
internationalization was a new idea, and someone proposed collation
support that relied on the OS today, the patch would be rejected in
about 2 minutes. The author would be pointed in the direction of
"Notes to Operator Class Implementors" within the nbtree README.

There are numerous user-visible benefits to ICU support, too, like:

* Case-insensitive collations become possible (with work in other
areas). No more contrib/citext hack. This is something that we seem to
want to work towards.

* Abbreviated keys in indexes with collated text becomes possible.
(Already mentioned that abbreviated keys for collated text + sorting
are effectively reintroduced.)

* More useful collations available for certain languages, such as
Japanese. Apparently, the JIS X 4061 algorithm produces results that
Japanese people find more useful, but glibc doesn't support it, and
never will.

* We might be able to document WAL compatibility usefully, now. The
documentation never gets around to explaining what two instances are
compatible for the purposes of physical replication. I can't think of
any other factor that prevents us from locking that down.

* Upgrade major OS versions without difficulty.

* User-defined collations, where you can mix and match certain facets
of how text is sorted as you please. Basically, ICU offers rich
functionality that we can bubble up to our users without too much
effort, as other database systems have.

-- 
Peter Geoghegan

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 2 Jul 2016 11:23:23 -0400

> this warning.  Patch attached.

Patch applied and backpatched.

---

From: Peter Eisentraut <[email-masked]>
Date: Sat, 9 Jul 2016 10:02:24 -0400

On 6/28/16 5:58 PM, Peter Geoghegan wrote:
> problems.

I plan to submit a patch for ICU support for September.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Peter Geoghegan <[email-masked]>
Date: Sat, 9 Jul 2016 14:48:04 -0700

> I plan to submit a patch for ICU support for September.

That's fantastic news! Your knowledge of packaging will be useful
here. I will review your patch.

-- 
Peter Geoghegan