Re: mysql_fdw trouble

On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:

> On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Dane Foster <studdugie(at)gmail(dot)com> writes:
>> > Installation and set up worked flawlessly but when I run the following
>> query
>> > ...
>> > ​I get the following error:​
>> > ​ERROR: failed to prepare the MySQL query:
>> > FUNCTION latest.btrim does not exist
>>
>> It looks like mysql_fdw is messing up by sending the trim() checks for
>> remote execution when there is no suitable function on the remote side.
>> Don't know whether that's a bug in mysql_fdw, or whether there's some
>> setup you're supposed to perform on the mysql server and have omitted.
>>
>> regards, tom lane
>>
> ​I think you are correct about mysql_fdw "... sending the trim() checks
> for remote execution" because according to the docs:
>
> "The latest version will push-down the foreign table where clause to the
> foreign server. The where condition on the foreign table will be executed
> on the foreign server hence there will be fewer rows to to bring across to
> PostgreSQL. This is a performance feature."
> I guess using mysql_fdw is a no-go for my data migration needs.
>
>
> Dane
> ​
> ​
>
​I'm not sure who to direct this question to but if the root cause is
really automatic push-down what about instea​d of automatic push-down of
the WHERE clause the mysql_fdw detected when the WHERE clause contained
PostgreSQL specific functions and not push the WHERE clause to MySQL? The
docs suggest that the old version did not push the WHERE clause down which
suggests that WHERE clause processing occurred on the PostgreSQL side. So
what if that PostgreSQL side WHERE clause processing code is revived and
used in the case where the WHERE clause shouldn't be pushed down?

This is all speculation of course and I don't have the time nor expertise
to go hacking on this idea. So I won't be offended if no one thinks it's a
good idea nor volunteers to write the code.

Dane​