Fixing string sorting issues in PostgreSQL after upgrading glibc

The Ubuntu 18.04 version of the glibc (GNU C) library contains significant changes in the string sorting rules. This change affects the way the data is displayed and the structure of indexes in PostgreSQL, which may violate the uniqueness of the primary key.

For example:

CREATE TABLE t (id int, str text PRIMARY KEY);
INSERT INTO t VALUES (1, 'yndkpx'), (2, 'ynd_kpx'), (3, 'ynd-kpx'), (4, 'kpx');

Sort result in SELECT * FROM t ORDER BY str; output:

• On Ubuntu 14.04:

   id |   str
  ----+---------
    1 | yndkpx
    2 | ynd_kpx
    3 | ynd-kpx
    4 | kpx
  

• On Ubuntu 18.04:

   id |   str
  ----+---------
    3 | ynd-kpx
    2 | ynd_kpx
    1 | yndkpx
    4 | kpx
  

Issue criticalityIssue criticality

Changing the sorting order has the following consequences:

• Violation of PRIMARY KEY uniqueness. PostgreSQL allows insertion of duplicates:

  INSERT INTO t VALUES (5, 'ynd_kpx');
  INSERT 0 1  -- The operation is successful, which is a violation
  

• Incorrect operation of indexes:

  Checking via amcheck:

  CREATE EXTENSION IF NOT EXISTS amcheck;
  SELECT bt_index_check('t_pkey');
  

  Result:

  ERROR:  item order invariant violated for index "t_pkey"
  DETAIL:  Lower index tid=(1,1) (points to heap tid=(0,1)) higher index tid=(1,2) 
  (points to heap tid=(0,5)) page lsn=0/1665F88.
  

• Changing string comparison results:

  -- Ubuntu 14.04:
  SELECT '1-1' < '11';
  
   ?column? 
  ----------
   f
  
  -- Ubuntu 18.04:
  SELECT '1-1' < '11';
  
   ?column? 
  ----------
   t
  

Technical solutionTechnical solution

The solution is implemented as an mdb-locales package, which includes:

• libmdblocales library for uploading locales.
• Patch for PostgreSQL.
• Fixed locales from the previous glibc version.

How it worksHow it works

mdb-locales provides interception of locale system calls followed by their redirection to the library, which stabilizes the sorting behavior:

-- After installing mdb-locales:
SELECT * FROM pg_collation WHERE collprovider='c';

Result:

  oid  |  collname  | collnamespace | collowner | collversion
-------+------------+---------------+-----------+-------------
 12547 | en_US.utf8 |            11 |        10 | 2.27
 12548 | en_US      |            11 |        10 | 2.27

VerificationVerification

Checking for correct operation after installing mdb-locales:

-- Checking uniqueness of PRIMARY KEY
INSERT INTO t VALUES (5, 'ynd_kpx');

ERROR:  duplicate key value violates unique constraint "t_pkey"

-- Checking index integrity
SELECT bt_index_check('t_pkey');

 bt_index_check 
---------------
               
(1 row)

Recommendations when upgrading Ubuntu to 18.04Recommendations when upgrading Ubuntu to 18.04

1. Before the upgrade:

   • Create a full backup of your data.
   • Audit the indexes.
   • Install and configure mdb-locales.

2. After the upgrade:

   • Perform index checks via amcheck.
   • Test critical queries with sorting.
   • Verify unique restrictions.