On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdugie@xxxxxxxxx> wrote:
On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Dane Foster <studdugie@xxxxxxxxx> 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:I guess using mysql_fdw is a no-go for my data migration needs."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."
Dane
I'm not sure who to direct this question to but if the root cause is really automatic push-down what about instead 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.
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
