Re: Query execution failure

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