before update trigger NEW modification to non updated field not reflected

[francoisp]

Hi All,
nice work mysql_FDW! I was pleasantly surprised to see that triggers can be created on foreign tables. Obviously these triggers are not called when CRUD is done on the mysqld end; I've written a deamon that does that, but I'm running into a problem.

I create a trigger on an imported foreign table. In the procedure, I change the value of a column that is not in the triggering update statement. This change does not make it to the mysql side.

CREATE OR REPLACE FUNCTION aatrigger_up() returns trigger
AS $$
DECLARE
BEGIN
	
	IF NOT(row_to_json(NEW)->'pgrti' is NULL) THEN
		NEW.pgrti = 2000000000*random();
	END IF;
        RAISE NOTICE 'aarigger_up %', row_to_json(NEW)::text;
  return NEW;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER aarigger_up BEFORE UPDATE ON mysql.users FOR EACH ROW EXECUTE PROCEDURE aarigger_up();
update mysql.users set email = '[email protected]' where id = 1;	

I can see that the value for pgrti is updated in the NOTICE in postgres. In mysql the value is not updated. If I add the target col to the statement it does go through

update mysql.users set email = '[email protected]', pgrti=0 where id = 1;	

I'd like my mysql pg_trigger calling to be transparent to someone using this on postgres. I need this to work to be able to detect CRUD coming from PG. Any idea where I'd change MYSQL_FDW to do this (also add fields that are updated in the trigger before firing off to mysql)?

TIA,
Francois

[francoisp]

I submitted a pull request #194 to fix this bug