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
Date: Wed, 05 Apr 2023 15:04:56 -0400
Message-ID: <2020213.1680721496@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel.westermann@dbi-services.com, 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: <2020213.1680721496@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox