BEFORE UPDATE trigger on postgres_fdw table not work - Mailing list pgsql-hackers
From | Shohei Mochizuki |
---|---|
Subject | BEFORE UPDATE trigger on postgres_fdw table not work |
Date | |
Msg-id | 201905270152.x4R1q3qi014550@toshiba.co.jp Whole thread Raw |
Responses |
Re: BEFORE UPDATE trigger on postgres_fdw table not work
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
List | pgsql-hackers |
Hi, I noticed returning a modified record in a row-level BEFORE UPDATE trigger on postgres_fdw foreign tables do not work. Attached patch fixes this issue. Below are scenarios similar to postgres_fdw test to reproduce the issue. postgres=# CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres',port '5432'); postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; postgres=# create table loc1 (f1 serial, f2 text); postgres=# create foreign table rem1 (f1 serial, f2 text) postgres-# server loopback options(table_name 'loc1'); postgres=# CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$ postgres$# BEGIN postgres$# NEW.f2 := NEW.f2 || ' triggered !'; postgres$# RETURN NEW; postgres$# END postgres$# $$ language plpgsql; postgres=# CREATE TRIGGER trig_row_before_insupd BEFORE INSERT OR UPDATE ON rem1 postgres-# FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); -- insert trigger is OK postgres=# INSERT INTO rem1 values(1, 'insert'); postgres=# SELECT * FROM rem1; f1 | f2 ----+-------------------- 1 | insert triggered ! (1 row) -- update trigger is OK if we update f2 postgres=# UPDATE rem1 set f2 = 'update'; postgres=# SELECT * FROM rem1; f1 | f2 ----+-------------------- 1 | update triggered ! Without attached patch: postgres=# UPDATE rem1 set f1 = 10; postgres=# SELECT * FROM rem1; f1 | f2 ----+-------------------- 10 | update triggered ! (1 row) f2 should be updated by trigger, but not. This is because current fdw code adds only columns to RemoteSQL that were explicitly targets of the UPDATE as follows. postgres=# EXPLAIN (verbose, costs off) UPDATE rem1 set f1 = 10; QUERY PLAN --------------------------------------------------------------------- Update on public.rem1 Remote SQL: UPDATE public.loc1 SET f1 = $2 WHERE ctid = $1 <--- not set f2 -> Foreign Scan on public.rem1 Output: 10, f2, ctid, rem1.* Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE (5 rows) With attached patch, f2 is updated by a trigger and "f2 = $3" is added to remote SQL as follows. postgres=# UPDATE rem1 set f1 = 10; postgres=# select * from rem1; f1 | f2 ----+-------------------------------- 10 | update triggered ! triggered ! (1 row) postgres=# EXPLAIN (verbose, costs off) postgres-# UPDATE rem1 set f1 = 10; QUERY PLAN ----------------------------------------------------------------------- Update on public.rem1 Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 -> Foreign Scan on public.rem1 Output: 10, f2, ctid, rem1.* Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE (5 rows) My patch adds all columns to a target list of remote update query as in INSERT case if a before update trigger exists. I tried to add only columns modified in trigger to the target list of a remote update query, but I cannot find simple way to do that because update query is built during planning phase at postgresPlanForeignModify while it is difficult to decide which columns are modified by a trigger until query execution. Regards, -- Shohei Mochizuki TOSHIBA CORPORATION
Attachment
pgsql-hackers by date: