From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9
 to RHEL 8.7
In-reply-to: 
 <GV0P278MB04193FBC28A1744C6945B81ED2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
References: 
 <GV0P278MB0419C826AF173592DA648C88D2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
 <2017352.1680720065@sss.pgh.pa.us>
 <GV0P278MB04193FBC28A1744C6945B81ED2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
Comments: In-reply-to "Daniel Westermann (DWE)"
 <daniel.westermann@dbi-services.com>
	message dated "Wed, 05 Apr 2023 18:51:51 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2020212.1680721496.1@sss.pgh.pa.us>
Date: Wed, 05 Apr 2023 15:04:56 -0400
Message-ID: <2020213.1680721496@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2020213.1680721496%40sss.pgh.pa.us>
Precedence: bulk

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
> Both instances use the same collation;

Yeah, doesn't look like you've made any configuration mistakes.
So either the two OSes sort differently, or there's index corruption
causing the indexscan to give bogus output.

The sample data you showed seemed to only involve numeric-ish strings,
which would be highly unlikely to change sort order across locale
updates.  But maybe there are weirder entries elsewhere in the column?

Anyway, the first thing I'd try is reindexing both tables --- doesn't
look like they're large enough to make that painful.  If that doesn't
fix it you must have a collation difference.  (Asking both systems
for a sorted dump of their cprd columns could help confirm that.)
You could probably hack around that, if an OS update isn't feasible,
by labelling the foreign table's column with some collation you aren't
using anywhere else in the local database.

			regards, tom lane