Re: MYSQL_FDW trigger BEFORE UPDATE changes to NEW on a col not in the update statement don't go through

[Etsuro Fujita]

Hi Francois,

On Wed, Apr 22, 2020 at 8:09 AM Francois Payette
 wrote:
> 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.

I'm not an expert on mysql_fdw, so maybe I'm missing something, but I
think we had the same issue in postgres_fdw.  See this:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8b6da83d162cb0ac9f6d21082727bbd45c972c53;hp=7dc6ae37def50b5344c157eee5e029a09359f8ee

Best regards,
Etsuro Fujita

MYSQL_FDW trigger BEFORE UPDATE changes to NEW on a col not in the update statement don't go through

[Francois Payette]

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 = 20*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 
 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