On Thu, Aug 7, 2014 at 03:07:04PM +0000, Matthew Kelly wrote: > We are currently running with the en_US.UTF-8 collation. It was a decision made long ago, and seeing as we never actually rely on the sort order of internationalized strings (other than for stability, apparently), we have never had any motivation to change this practice. > > Some way of versioning collations, which is not tied to glibc seems immensely appealing. Without a good way of testing the differences between glibc sort versions, it seems the only safe thing to do at the moment is to guarantee all streaming replica's run from the exact same OS image. Which is fine until you want to upgrade your OS, and need to do a dump-restore instead of being able to do that in a rolling fashion. > > > > To Bruce's point the way I was able to test for this issue in a particular index was (approximately): > --Assuming textfield is what the index is on, this causes the query planner to scan the index and give the position in the index. > CREATE TABLE index_order (SELECT textfield, dense_rank() OVER (ORDER BY textfield) as i_order FROM table); > --No index here, postgres must sort > CREATE TABLE both_order as (SELECT textfield, i_order, dense_rank() OVER (ORDER BY textfield) as sort_order FROM index_order); > -- If this doesn't return zero, you have a problem > SELECT count(*) FROM both_orders WHERE i_order <> sort_order; > > This method is really slow on a big table, and I'm not going to promise it always works, but that is how we found the root cause. We could walk the index looking for inconsistent btree splits, e.g. the split doesn't match the ordering returned by the existing collation functions. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
