On Wed, Dec 7, 2016 at 10:40 PM, Maeldron T. <maeldron@xxxxxxxxx> wrote: > Anyway, ICU is turned on for PostgreSQL 9.6 even in the pkg version. Hurray. Hmm, a curious choice, considering that FreeBSD finally has built-in collations that work! Using the port's ICU patch doesn't change anything about the risks here as far as I know. ICU continually pulls corrections and improvements from CLDR, and FreeBSD's libc is now doing the same, so actually both ICU and libc might be getting the very same stream of collation changes, just at different speeds. IMHO we can't continue to ignore this problem: we need to teach Postgres to recognise when collations change. That could mean adopting like ICU and then tracking when every index is potentially invalidated by a version change (see Peter Eisentraut's proposal), or adopting non-portable implementation-specific techniques. For the latter, we'd probably not want to litter core Postgres with assumptions about how each OS does things. One fairly blunt approach I came up with goes like this: 1. Add a new optional GUC system_collation_version_command = '/some/user/supplied/script.sh'. 2. When postmaster starts, run it and remember the output in memory. 3. When a database is created, store it for this database. 4. When connecting to a database, complain loudly if version at startup doesn't match the stored version. 5. Update the stored value to the startup value when you REINDEX DATABASE (ie that's how to clear the warning). Then package mantainers could supply a script that know how to do the right thing on this OS. For example it could be the package version string from the currently installed locales package, or an MD5 hash of the contents of all files in /usr/share/locales/, or whatever suits. The reason for only running the script at postmaster startup is that there is a risk of libc caching data, so that a REINDEX would use old data but running the command would see new files on disk, so we need to make sure that a cluster restart is necessary after upgrading your OS to clear the warning. That's horribly blunt: it makes you reindex the whole database even if you don't use a collation that changed, or don't even use btrees, etc. You could do something more nuanced and complicated that works at the level of individual locales and indexes (see the link I posted earlier to check_pg_collations for some ideas), but the idea would be basically the same. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
