Debian 9.1. PostgreSQL 9.6 (also tried 9.5, 10) Latest mysql_fdw from master branch.
Test foreign table:
CREATE FOREIGN TABLE public.main_address (... id character varying(64) , ... sattelite integer , ... ) SERVER my_test OPTIONS (dbname 'mon', table_name 'address');
Test function with multiple queries:
`CREATE OR REPLACE FUNCTION public.stress_test()
RETURNS void AS
$BODY$
DECLARE
startid integer;
endid integer;
BEGIN
startid := 0;
LOOP
startid := startid + 10;
endid := startid + 10;
RAISE NOTICE 'copying data from id % to id %', startid, endid;
PERFORM id
FROM main_address
WHERE (sattelite = startid OR sattelite = endid);
PERFORM pg_sleep(0.1);
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;`
Result:
SELECT public.stress_test();
NOTICE: copying data from id 10 to id 20
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
CONTEXT: SQL-operator : "SELECT id
FROM main_address
WHERE (sattelite = startid OR sattelite = endid)"
function PL/pgSQL stress_test(), row 14, operator PERFORM
NOTICE: copying data from id 20 to id 30
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
NOTICE: copying data from id 30 to id 40
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
NOTICE: copying data from id 40 to id 50
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
NOTICE: copying data from id 50 to id 60
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
NOTICE: copying data from id 60 to id 70
CONTEXT: function PL/pgSQL stress_test(), row 12, operator RAISE
ERROR: cannot convert constant value to MySQL value
HINT: Constant value data type: 0
CONTEXT: SQL-operator: "SELECT id
FROM main_address
WHERE (sattelite = startid OR sattelite = endid)"
функция PL/pgSQL stress_test(), row 14, operator PERFORM
********** Error **********
ERROR: cannot convert constant value to MySQL value
SQL-condition: HV004
HINT: Constant value data type: 0
CONTEXT: SQL-operator : "SELECT id
FROM main_address
WHERE (sattelite = startid OR sattelite = endid)"
function PL/pgSQL stress_test(), row 14, operator PERFORM
Also in syslog I've:
kernel: [8389070.645456] postgres[45009]: segfault at 10 ip 00007f74ca6484a0 sp 00007fff6e565510 error 6 in mysql_fdw.so[7f74ca63f000+d000]
On mysql server query log:
770677 Connect monitor@192.168.90.88 on monitoring
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 10) OR (`sattelite` = 20)))
770677 Execute SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 10) OR (`sattelite` = 20)))
770677 Close stmt
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 20) OR (`sattelite` = 30)))
770677 Execute SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 20) OR (`sattelite` = 30)))
770677 Close stmt
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 30) OR (`sattelite` = 40)))
770677 Execute SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 30) OR (`sattelite` = 40)))
770677 Close stmt
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 40) OR (`sattelite` = 50)))
770677 Execute SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 40) OR (`sattelite` = 50)))
770677 Close stmt
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 50) OR (`sattelite` = 60)))
770677 Execute SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = 50) OR (`sattelite` = 60)))
770677 Close stmt
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Query SET time_zone = '+00:00'
770677 Query SET sql_mode='ANSI_QUOTES'
770677 Prepare SELECT `id` FROM `monitoring`.`address` WHERE (((`sattelite` = ?) OR (`sattelite` = ?)))
Debian 9.1. PostgreSQL 9.6 (also tried 9.5, 10) Latest mysql_fdw from master branch. Test foreign table:
CREATE FOREIGN TABLE public.main_address (... id character varying(64) , ... sattelite integer , ... ) SERVER my_test OPTIONS (dbname 'mon', table_name 'address');
Test function with multiple queries:Result:
Also in syslog I've:
kernel: [8389070.645456] postgres[45009]: segfault at 10 ip 00007f74ca6484a0 sp 00007fff6e565510 error 6 in mysql_fdw.so[7f74ca63f000+d000]
On mysql server query log: