Issue
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;
Solution
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.
Answered By - nck Answer Checked By - Mildred Charles (WPSolving Admin)