pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Pete Storer <Pete.Storer@sas.com>
To: Christophe Pettus <xof@thebuild.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Query execution failure
Date: Mon, 30 Jan 2023 15:04:58 -0500
Message-ID: <1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com> (raw)
In-Reply-To: <SN6PR05MB6637045A3875380FFE2399D3F3D39@SN6PR05MB6637.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>
	<2961799.1674749201@sss.pgh.pa.us>
	<Y9fjfNpP7HZh1YX9@momjian.us>
	<f0c0bfec-0571-8711-deee-f6e679846c18@joeconway.com>
	<490923.1675096173@sss.pgh.pa.us>
	<SN6PR05MB66377701BE8410F7D869CBCEF3D39@SN6PR05MB6637.namprd05.prod.outlook.com>
	<B29929A8-448D-49FD-A041-D70D1BBE455A@thebuild.com>
	<SN6PR05MB6637045A3875380FFE2399D3F3D39@SN6PR05MB6637.namprd05.prod.outlook.com>

On 1/30/23 13:24, Pete Storer wrote:
> Makes sense - but I'm using the PG substring function here.
> 
> LEFT OUTER JOIN main.ship_event se ON (se.ship_event_nm = substring(dp.shipevent from 6 for 5))
> 
> In this case, the dp.shipevent is in the fdw-accessed MySql table. Shouldn't that force the sort to be local?

Looking at the mysql-fdw source (which looks pretty much the same as 
postgres-fdw at first glance):
8<-------------
/* We don't support cases where there are any SRFs in the targetlist */
if (parse->hasTargetSRFs)
	return;
8<-------------

Based on that, perhaps a kluge (and completely untested) workaround is to:
1/ create an SRF that returns one column, one row constant
2/ add the SRF to your targetlist

The SRF likely needs to be plpgsql to avoid inlining, maybe something like:
8<-------------
CREATE OR REPLACE FUNCTION theanswer() RETURNS setof int as $$
BEGIN
  RETURN NEXT 42;
END;
$$ LANGUAGE plpgsql;
8<-------------

HTH,
-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com






view thread (18+ messages)  latest in thread

Message-ID: <1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com>
Permalink:  ../1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com/
Also on:    postgresql.org/message-id/1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com

 · 

reply
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: mail@joeconway.com, Pete.Storer@sas.com, xof@thebuild.com, tgl@sss.pgh.pa.us, bruce@momjian.us, pgsql-bugs@lists.postgresql.org
  Subject: Re: Query execution failure
  In-Reply-To: <1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com>

* 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