From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pete Storer <Pete.Storer@sas.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: FW: Query execution failure
In-reply-to: 
 <BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
References: 
 <BL0PR05MB66282A9070EE621D031C27C7F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
 <BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
Comments: In-reply-to Pete Storer <Pete.Storer@sas.com>
	message dated "Wed, 25 Jan 2023 22:17:07 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2957100.1674746628.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 26 Jan 2023 10:23:48 -0500
Message-ID: <2957101.1674746628@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2957101.1674746628%40sss.pgh.pa.us>
Precedence: bulk

Pete Storer <Pete.Storer@sas.com> writes:
> When trying to run a complex join of eight tables - including 6 foreign =
tables - the query runs for a few seconds and then terminates with an erro=
r message
> ERROR: mergejoin input data is out of order

You have not shown us the table definitions, nor an EXPLAIN for the
query, but this'd indicate that the sort ordering of one or more
tables is not like that of the others.  Likely causes of this include

* corrupt index, if an indexscan is being used instead of an explicit
  sort

* foreign server has a different idea of the locale's sort details than
  the local server does (or than some other foreign server, if the
  query touches more than one)

If the join key columns are all numeric then it's almost surely a corrupt
index somewhere.  But if you are joining on string keys then you are
subject to the whims of the locale's sort order, and that is a mess,
particularly for non-ASCII data.  Not only might it be different on
different platforms, but even on the same platform it can change over
time (with the effect of making indexes on string columns corrupt).
There's some details and advice here:

https://wiki.postgresql.org/wiki/Locale_data_changes

			regards, tom lane