WHERE clause isn't pushed after multiple SELECTs from a function/do

When a SELECT is executed multiple times inside a function or DO-block, mysql_fdw stops to push the WHERE clause down to mysql after 5 executions.

A very simple and crude example of this behavior:

DO $$
DECLARE
    startid BIGINT;
    endid BIGINT;
BEGIN
    startid := 0;

    LOOP
        startid := startid + 1000;
        endid := startid + 1000;

        RAISE NOTICE 'copying data from id % to id %', startid, endid;

        PERFORM id
        FROM remotetable
        WHERE id BETWEEN startid AND endid;

        PERFORM pg_sleep(0.5);
    END LOOP;
END $$;

NOTICE:  copying data from id 1000 to id 2000
NOTICE:  copying data from id 2000 to id 3000
NOTICE:  copying data from id 3000 to id 4000
NOTICE:  copying data from id 4000 to id 5000
NOTICE:  copying data from id 5000 to id 6000
NOTICE:  copying data from id 6000 to id 7000

Now mysql_fdw justs sits there and collects all rows from mysql (several million in my case). Postgresql is unresponsive and the connection has to be killed in MySQL.

I am using postgresql 9.4.4, mysql 5.6.25 and mysql_fdw 97006df765a15c8705dee921577ed992a57e1bba.

Note that https://github.com/EnterpriseDB/mysql_fdw/issues/44 also stopped working after 5 executions and also affects the usage inside functions.

I have tried the the same test case and it works fine on my computer.

postgres=# select count(*) from remotetable;

count

100000 (1 row)

postgres=# DO $$ DECLARE startid BIGINT; endid BIGINT; BEGIN startid := 0;

LOOP
    startid := startid + 1000;
    endid := startid + 1000;

    RAISE NOTICE 'copying data from id % to id %', startid, endid;

    PERFORM id
    FROM remotetable
    WHERE id BETWEEN startid AND endid;

    PERFORM pg_sleep(0.5);
END LOOP;

END $$; NOTICE: copying data from id 1000 to id 2000 NOTICE: copying data from id 2000 to id 3000 NOTICE: copying data from id 3000 to id 4000 NOTICE: copying data from id 4000 to id 5000 NOTICE: copying data from id 5000 to id 6000 NOTICE: copying data from id 6000 to id 7000 NOTICE: copying data from id 7000 to id 8000 NOTICE: copying data from id 8000 to id 9000 NOTICE: copying data from id 9000 to id 10000 NOTICE: copying data from id 10000 to id 11000 NOTICE: copying data from id 11000 to id 12000 NOTICE: copying data from id 12000 to id 13000 NOTICE: copying data from id 13000 to id 14000 NOTICE: copying data from id 14000 to id 15000 NOTICE: copying data from id 15000 to id 16000 NOTICE: copying data from id 16000 to id 17000 ...

On Sat, Aug 1, 2015 at 5:51 PM, luto notifications@github.com wrote:

When a SELECT is executed multiple times inside a function or DO-block, mysql_fdw stops to push the WHERE clause down to mysql after 5 executions.

A very simply and crude example of this behavior:

DO $$ DECLARE startid BIGINT; endid BIGINT;BEGIN startid := 0;
LOOP
    startid := startid + 1000;
    endid := startid + 1000;

    RAISE NOTICE 'copying data from id % to id %', startid, endid;

    PERFORM id
    FROM remotetable
    WHERE id BETWEEN startid AND endid;

    PERFORM pg_sleep(0.5);
END LOOP;
END $$;

NOTICE: copying data from id 1000 to id 2000 NOTICE: copying data from id 2000 to id 3000 NOTICE: copying data from id 3000 to id 4000 NOTICE: copying data from id 4000 to id 5000 NOTICE: copying data from id 5000 to id 6000 NOTICE: copying data from id 6000 to id 7000

Now mysql_fdw justs sits there and collects all rows from mysql (several million in my case). Postgresql is unresponsive and the connection has to be killed in MySQL.

I am using postgresql 9.4.4, mysql 5.6.25 and mysql_fdw 97006df https://github.com/EnterpriseDB/mysql_fdw/commit/97006df765a15c8705dee921577ed992a57e1bba .

Note that #44 https://github.com/EnterpriseDB/mysql_fdw/issues/44 also stopped working after 5 executions and also affects the usage inside functions.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/65.

EnterpriseDB / mysql_fdw

WHERE clause isn't pushed after multiple SELECTs from a function/do #65

count