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
Date: Thu, 26 Jan 2023 10:23:48 -0500
Message-ID: <2957101.1674746628@sss.pgh.pa.us> (raw)
In-Reply-To: <BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
References: <BL0PR05MB66282A9070EE621D031C27C7F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
<BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
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 error 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
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, Pete.Storer@sas.com, pgsql-bugs@lists.postgresql.org
Subject: Re: FW: Query execution failure
In-Reply-To: <2957101.1674746628@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