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 11:06:41 -0500
Message-ID: <2961799.1674749201@sss.pgh.pa.us> (raw)
In-Reply-To: <BL0PR05MB66282D22D96AB745FBBD521FF3CF9@BL0PR05MB6628.namprd05.prod.outlook.com>
References: <BL0PR05MB66282A9070EE621D031C27C7F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
<BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
<2957101.1674746628@sss.pgh.pa.us>
<BL0PR05MB66282D22D96AB745FBBD521FF3CF9@BL0PR05MB6628.namprd05.prod.outlook.com>
Pete Storer <Pete.Storer@sas.com> writes:
> I'm not sure how to identify which index may be corrupt, if that is the cause. Regarding a different sort order between tables, if that were the case, wouldn't this query fail in Production as it does in our Development environment? They are identical with respect to indices and sort order, and they both point to the same foreign table, which resides on a MySQL database.
Meh. You've just got two merge joins in that plan, and three of the
four inputs to them are being explicitly sorted, so one would certainly
hope that those sorts are consistent. But the fourth input is being
sourced directly from the foreign server, and so this query is
fundamentally assuming that the foreign server has the same idea of
sort ordering as the local one. Evidently that assumption is wrong.
I'd check locale settings on your two databases to understand why
it doesn't fail on prod. But the long-term solution if you want this
to work reliably is probably to force use of C locale on all three
databases; that's about the only locale that you can fully trust
to be portable.
I don't know anything about the mysql FDW, but another possible route
to fixing things is to get it to not believe that the remote's sort
ordering matches the local one. If the plan were relying on a local
sort instead of a remote sort then everything would be fine.
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: <2961799.1674749201@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