Re: mysql_fdw trouble

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/29/2015 12:10 PM, Dane Foster wrote:
>
>> On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce(at)hogranch(dot)com
>> <mailto:pierce(at)hogranch(dot)com>> wrote:
>>
>> On 10/29/2015 11:20 AM, Dane Foster wrote:
>>
>>> ​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."
>>>
>>>
>> the alternative would be to fetch the whole table across the FDW
>> interface, then run the where locally, for a large table where
>> you're only selecting a few rows, this would be very painful.
>>
>> I guess using mysql_fdw is a no-go for my data migration needs.
>>>
>>
>> or, rewrite that WHERE clause to be mysql compatible.
>>
>> Easier said than done because the LENG​TH and TRIM functions both exist
>> in MySQL but I guess under the covers in PostgreSQL btrim is being
>> invoked when TRIM is called therefore that is what is being "pushed
>> down" to the MySQL and there is nothing I can do about that.
>>
>> I guess I could leave out the call to trim, and copy the data into a
>> temp table on the PostgreSQL side, and blah blah blah. My point being
>> why should I have to jump through hoops because mysql_fdw is broken?
>> I'll just go back to writing the migration script as a PHP program
>> because if mysql_fdw didn't exist that's what I would have to do anyway.
>>
>
> Remember you are using a Beta version of Postgres, so it is not entirely
> unexpected that things might be broken, especially when working with
> non-core extensions. In the spirit of testing, that Beta implies, why not
> help fix mysql_fdw by filing an issue? If you already have, my apologies.
>
​I'm fully aware of that fact and gladly accept my responsibility which is
why I have opened an issue:
https://github.com/EnterpriseDB/mysql_fdw/issues/70

For me reporting the issue in the hopes that they will fix it is a separate
issue from expending energy working around the bug because the great thing
about the procedural code is that it's littered w/ the same SQL that a pure
SQL migration script would contain. So if they fix it in reasonable amount
of time then all that's required to create a pure SQL migration script is
copy/paste.

Dane​

>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>> ​Dane​
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>