EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
523 stars 161 forks source link

Reading and writing on two tables in diffirent databases on one foreign mysql server #173

Open hombre7 opened 5 years ago

hombre7 commented 5 years ago

Test tables:

CREATE FOREIGN TABLE ft.axxx
   (id integer)
   SERVER a44
   OPTIONS (dbname 'a44', table_name 'a_xxxx');
CREATE FOREIGN TABLE ft.sxxx
   (id integer)
   SERVER a44
   OPTIONS (dbname 'a55', table_name 'a_yyyy');

Test procedure works:


 CREATE OR REPLACE FUNCTION testfdw()
 RETURNS integer AS
 $BODY$
 declare cnt INTEGER;
 begin
 SELECT count(*) FROM ft.axxx INTO cnt;
 SELECT count(*) FROM ft.sxxx into cnt;
 return cnt;
 end;

Test procedure fails:


 CREATE OR REPLACE FUNCTION testfdw()
 RETURNS integer AS
 $BODY$
 declare cnt INTEGER;
 begin
 SELECT count(*) FROM ft.axxx INTO cnt;
INSERT INTO ft.sxxx(id) VALUES (23);
 return cnt;
 end;

ERROR: failed to execute the MySQL query: Table 'a44.a_yyyy' doesn't exist

Seems that it ignores dbname option of foreign table: In MySQL log I see:

EXPLAIN a_yyyy. Creating a blank table in mysql a44.a_yyyy solved the problem, now EXPLAIN works for db a44 and INSERT works for db a55.