mysql_fdw trouble

On 10/29/2015 10:47 AM, Dane Foster wrote:

Hello,

I have a MySQL/PHP app that I want to port to PostgreSQL so I just
installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
because I'd like to do the data migration in SQL if possible.

Installation and set up worked flawlessly but when I run the following query
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;

​I get the following error:​
​ ​

​ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist

FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
tables.​

​Any help would be appreciated.

What version of Postgres are you using?

Thanks,
​

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 29, 2015 at 1:56 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/29/2015 10:47 AM, Dane Foster wrote:

Hello,

I have a MySQL/PHP app that I want to port to PostgreSQL so I just
installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
because I'd like to do the data migration in SQL if possible.

Installation and set up worked flawlessly but when I run the following
query
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;

​I get the following error:​
​ ​

​ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist

FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
tables.​

​Any help would be appreciated.

What version of Postgres are you using?

Thanks,
​

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com

​
My bad. I should know better. It's PostgreSQL 9.5beta1 on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit

Thanks,

Dane
​

Dane Foster <studdugie@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dane Foster <studdugie@gmail.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
​

On 10/29/2015 11:20 AM, Dane Foster wrote:

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

Dane Foster <studdugie@gmail.com <mailto:studdugie@gmail.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.

Before you give up I would file an issue here:

https://github.com/EnterpriseDB/mysql_fdw/issues

including the MySQL version also.

This issue:

https://github.com/EnterpriseDB/mysql_fdw/issues/42

indicates this type of thing has come up before and was fixed.

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
john r pierce, recycling bits in santa cruz

On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce@hogranch.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.

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

On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.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.

--
john r pierce, recycling bits in santa cruz

​Dane​

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver <adrian.klaver@aklaver.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@hogranch.com
<mailto:pierce@hogranch.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​

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

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@hogranch.com <mailto:pierce@hogranch.com>
<mailto:pierce@hogranch.com <mailto:pierce@hogranch.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@aklaver.com>
wrote:

On Thu, Oct 29, 2015 at 8:22 PM, CaT <cat@zip.com.au> wrote:

On Thu, Oct 29, 2015 at 08:38:49PM -0400, Dane Foster wrote:

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

Then it should be a new thread or the old post should not be quoted.

So there! ;)

--
"A search of his car uncovered pornography, a homemade sex aid, women's
stockings and a Jack Russell terrier."
-
http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480

​
Well played sir!

Dane
​

On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dane Foster <studdugie@gmail.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​

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com

​
I get rows of data, location and all.

Dane
​

On 10/30/2015 07:21 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

​
I get rows of data, location and all.

And when you do?:

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') ||
']')::TSTZRANGE AS duration
FROM
_series
) AS v

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/30/2015 07:21 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

​
I get rows of data, location and all.

And when you do?:

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') ||
']')::TSTZRANGE AS duration
FROM
_series
) AS v

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com

​Before I answer your second query question I need to revise my response to
the first. Yes the first query runs w/o an error message but the bit about
"rows and all" was not entirely correct. Out of 313 rows only the first row
had a location. The other 312 rows have NULL in the location column which
is not supposed to happen. To verify this I changed the table names and
removed the PostgreSQL transformations (i.e., use of || and :: for casting)
and ran the query against the MySQL database; it returned 313 rows of data,
location and all.

Now that I've cleared that up.

Your second query also runs w/o any error messages but like the first only
the first row has a non NULL value in the location column.

Dane
​

On 10/30/2015 08:13 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/30/2015 07:21 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>> wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS
location,
('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>

​
I get rows of data, location and all.

And when you do?:

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') ||
']')::TSTZRANGE AS duration
FROM
_series
) AS v

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

​Before I answer your second query question I need to revise my response
to the first. Yes the first query runs w/o an error message but the bit
about "rows and all" was not entirely correct. Out of 313 rows only the
first row had a location. The other 312 rows have NULL in the location
column which is not supposed to happen. To verify this I changed the
table names and removed the PostgreSQL transformations (i.e., use of ||
and :: for casting) and ran the query against the MySQL database; it
returned 313 rows of data, location and all.

You would think that would also cause an issue with the first row that
is returned correctly. My suspicion is with this:

row_number() OVER (PARTITION BY cid ORDER BY lower(duration))

What happens if you run the full SELECT without it?

Now that I've cleared that up.

Your second query also runs w/o any error messages but like the first
only the first row has a non NULL value in the location column.

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/30/2015 08:13 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/30/2015 07:21 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.com>>> wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

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,​

So what do you get when you do?:

SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS
location,
('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);

Dane

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.com>>

​
I get rows of data, location and all.

And when you do?:

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') ||
']')::TSTZRANGE AS duration
FROM
_series
) AS v

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

​Before I answer your second query question I need to revise my response
to the first. Yes the first query runs w/o an error message but the bit
about "rows and all" was not entirely correct. Out of 313 rows only the
first row had a location. The other 312 rows have NULL in the location
column which is not supposed to happen. To verify this I changed the
table names and removed the PostgreSQL transformations (i.e., use of ||
and :: for casting) and ran the query against the MySQL database; it
returned 313 rows of data, location and all.

You would think that would also cause an issue with the first row that is
returned correctly. My suspicion is with this:

row_number() OVER (PARTITION BY cid ORDER BY lower(duration))

What happens if you run the full SELECT without it?

Now that I've cleared that up.

Your second query also runs w/o any error messages but like the first
only the first row has a non NULL value in the location column.

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com

​
Your first query didn't use it and as discussed rows come back but only the
first row has a non NULL location column.

Dane
​