Another oddity in handling of WCO constraints in postgres_fdw

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>

From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: pgsql-hackers@postgresql.org
Date: 2017-09-28T12:15:29Z
Lists: pgsql-hackers

Attachments

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.

Best regards,
Etsuro Fujita

Commits

  1. Fix WITH CHECK OPTION on views referencing postgres_fdw tables.

  2. Allow insert and update tuple routing and COPY for foreign tables.

  3. When WCOs are present, disable direct foreign table modification.