On Thu, Sep 28, 2017 at 5:45 PM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp> wrote: > Hi, > > Commit 7086be6e3627c1ad797e32ebbdd232905b5f577f addressed mishandling of WCO > in direct foreign table modification by disabling it when we have WCO, but I > noticed another oddity in postgres_fdw: > > postgres=# create table base_tbl (a int, b int); > postgres=# create function row_before_insupd_trigfunc() returns trigger as > $$begin new.a := new.a + 10; return new; end$$ language plpgsql; > postgres=# create trigger row_before_insupd_trigger before insert or update > on base_tbl for each row execute procedure row_before_insupd_trigfunc(); > postgres=# create server loopback foreign data wrapper postgres_fdw options > (dbname 'postgres'); > postgres=# create user mapping for CURRENT_USER server loopback; > postgres=# create foreign table foreign_tbl (a int, b int) server loopback > options (table_name 'base_tbl'); > postgres=# create view rw_view as select * from foreign_tbl where a < b with > check option; > > So, this should fail, but > > postgres=# insert into rw_view values (0, 5); > INSERT 0 1 > > The reason for that is: this is processed using postgres_fdw's non-direct > foreign table modification (ie. ForeignModify), but unlike the RETURNING or > local after trigger case, the ForeignModify doesn't take care that remote > triggers might change the data in that case, so the WCO is evaluated using > the data supplied, not the data actually inserted, which I think is wrong. > (I should have noticed that as well while working on the fix, though.) So, > I'd propose to fix that by modifying postgresPlanForeignModify so that it > handles WCO the same way as for the RETURNING case. Attached is a patch for > that. I'll add the patch to the next commitfest. >
Enforcing WCO constraints imposed by the local server on the row/DML being passed to the foreign server is fine, but trying to impose them on the row being inserted/updated at the foreign server looks odd. May be we should just leave this case as it is. I am comparing this case with the way we handle constraints on a foreign table. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers