Re: mysql_fdw trouble

[Dane Foster <studdugie@xxxxxxxxx>]

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.klaver@xxxxxxxxxxx> wrote:

On 10/29/2015 12:56 PM, Dane Foster wrote:

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 10/29/2015 12:10 PM, Dane Foster wrote:

        On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce
        <pierce@xxxxxxxxxxxx <mailto:pierce@xxxxxxxxxxxx>

        <mailto:pierce@xxxxxxxxxxxx <mailto:pierce@xxxxxxxxxxxx>>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx