Hi All,
I was pleasantly surprised to see that triggers can be created on FDW tables.
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 = 'ad...@example.com' 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 = 'ad...@example.com', pgrti=0 where id = 1;
I need this to work to be able to detect CRUD coming from PG in a little
deamon that calls pg_triggers for updates coming from mysqld; without a means
to detect changes originating from pg the triggers would fire twice. 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)?
I’m seeing in https://github.com/EnterpriseDB/mysql_fdw/blob/master/deparse.c
<https://github.com/EnterpriseDB/mysql_fdw/blob/master/deparse.c> in
mysql_deparse_update
That the actual update statement is used to generate the mapping, so any col
referred to in triggers would be ignored…
TIA, stay safe!
Francois Payette
