skip to Main Content

I have a problem with the following sorting:

SELECT name FROM unnest(ARRAY[
    'MIR1290', 'MIR1291', 'MIR129-1', 'MIR1292', 'MIR129-2', 'MIR1293', 'MIR1294', 'MIR1296', 'MIR1297', 'MIR1298', 'MIR1299'
]) name ORDER BY name;

In one server is sorted as follows:

"name"
"MIR1290"
"MIR129-1"
"MIR1291"
"MIR129-2"
"MIR1292"
"MIR1293"
"MIR1294"
"MIR1296"
"MIR1297"
"MIR1298"
"MIR1299"

And in the other one as follows:

"name"
"MIR1290"
"MIR1291"
"MIR129-1"
"MIR1292"
"MIR129-2"
"MIR1293"
"MIR1294"
"MIR1296"
"MIR1297"
"MIR1298"
"MIR1299"

As you can see “MIR1291” and “MIR129-1” are sorted differently in both servers for some reason. The database has been dumped in both servers from the same file so it shouldn’t be a problem from the database.
The locale of both servers is the same:

LANG=en_US.UTF-8
LANGUAGE=en_US:en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

The config of postgres and version is the same on both servers, the output of ‘SHOW ALL’ only differs on:

 max_connections                     | 500                                      | Sets the maximum number of concurrent connections.

pg_config is the same:

    BINDIR = /usr/lib/postgresql/9.6/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.6
HTMLDIR = /usr/share/doc/postgresql-doc-9.6
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.6/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/9.6/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.6/man
SHAREDIR = /usr/share/postgresql/9.6
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-tclconfig=/usr/lib/x86_64-linux-gnu/tcl8.6' '--with-includes=/usr/include/tcl8.6' 'PYTHON=/usr/bin/python' '--mandir=/usr/share/postgresql/9.6/man' '--docdir=/usr/share/doc/postgresql-doc-9.6' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/9.6' '--bindir=/usr/lib/postgresql/9.6/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-tap-tests' '--enable-debug' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' '--with-systemd' 'CFLAGS=-g -O2 -specs=/usr/share/dpkg/no-pie-compile.specs -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer' 'LDFLAGS=-specs=/usr/share/dpkg/no-pie-link.specs -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5' '--with-krb5' '--with-gssapi' '--with-ldap' '--with-selinux' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.6
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -specs=/usr/share/dpkg/no-pie-compile.specs -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer
CFLAGS_SL = -fPIC
LDFLAGS = -specs=/usr/share/dpkg/no-pie-link.specs -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6.17

The only relevant difference that I think thee is between both servers is the kernel:

4.9.0-8-amd64 #1 SMP Debian 4.9.130-2 (2018-10-27)
4.9.0-9-amd64 #1 SMP Debian 4.9.168-1+deb9u3 (2019-06-16)

Does anyone know what can influence that different sorting between the two installations?

Edit:
Some more information, the following query returns the same in both servers:

select datname, 
       datcollate
from pg_database;

Returns:

    datname    | datcollate
---------------+-------------
 postgres      | en_US.UTF-8
 mydb          | en_US.UTF-8

And the following does not return anything in any of the servers:

select table_schema, 
       table_name, 
       column_name,
       collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
         table_name,
         ordinal_position;

2

Answers


  1. Chosen as BEST ANSWER

    Thanks to the comment provided by @a_horse_with_no_name I got the problem solved. This library was different in the server:

    ldd (Debian GLIBC 2.24-11+deb9u4) 2.24 
    ldd (Debian GLIBC 2.30-2) 2.30 
    

    And that seems to cause this issue. After upgrading it both systems threw the same results.


  2. Specify the COLLATE to try get the same result on both servers.

    SELECT name 
    FROM unnest(ARRAY[
        'MIR1290', 'MIR1291', 'MIR129-1', 'MIR1292', 'MIR129-2'
      , 'MIR1293', 'MIR1294', 'MIR1296', 'MIR1297', 'MIR1298', 'MIR1299'
    ]) name 
    ORDER BY name COLLATE "en-US-x-icu";
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search