Hi Tom,
Thanks very much for your quick response. Given below are the details of Collate and Ctype.
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+---------+-----------+---------+-------+---------------------
bs_datastore | bsdata | SQL_ASCII | C | C |
We are actually doing upgrade using pg_upgrade from 9.3.12 to 9.6.12 on the same operating system.(We have not upgraded OS or glibc). Also the above data is same before and after pg_upgrade.
Encoding and locale we always pass during initdb. Since we are passing these settings explicitly I don’t think they are derived from OS as per our understanding. Please correct us if we are wrong.
OS Details:
cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)
Glibc version: 2.17
Please let me know in case if you need any further details.
Thanks,
Suresh
On 15/04/19, 9:45 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> When we upgraded Postgres from 9.3.12 to 9.6.12 we observed that queries
> like the following are no longer giving the expected results.
> select * from xyz.job_attr where jobid = 'foo';
> [ but reindexing fixed it ]
What collation locale do you use in that database? (psql \l's
"Collate" column would say.) If it's not "C", the most likely
explanation is that you also upgraded the underlying OS, and glibc's
sort order for that locale changed from what it was before. If so,
reindexing is the appropriate cure. There isn't a lot we can do about
this inside Postgres, because we have no visibility into whether a
locale's behavior is different today than it was yesterday :-(
regards, tom lane