Re: mysql_fdw trouble

Hello,

I think I've tripped over another mysq_fdw bug. I've filed a bug report on
github already but just in case the problem is w/ my query I figured I
would post it here in case someone sees something obvious.

The error message I get is: null value in column "location" violates
not-null constraint.

The DDL is here: https://github.com/EnterpriseDB/mysql_fdw/issues/71

For the record I know top posting is a crime against god and humanity but I
feel justified because this post is not directly related to the original.
So there! Granted it's in the same milieu; and yes this current sentence
exists for the sole purpose of me being able to use the word milieu because
the opportunity to use it is so few and far between.

​INSERT INTO series (cid, day, title, description, location, duration,
can_join)
SELECT
cid,
row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
title,
description,
location,
duration,
can_join
FROM (
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSZ_PERIOD AS duration
FROM
_series
) AS v​

​Regards,​

Dane

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

> On 10/29/2015 12:56 PM, Dane Foster wrote:
>
>> On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto: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>
>> <mailto: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
>>
>
> Great and thanks.
>
>
>> 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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>