Another oddity in handling of WCO constraints in postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Another oddity in handling of WCO constraints in postgres_fdw
Date: 2017-09-28 12:15:29
Message-ID: 7eb58fab-fd3b-781b-ac33-f7cfec96021f@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
fix-wco-handling-in-postgres-fdw.patch text/plain 18.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-09-28 12:21:37 Re: pgsql: Add test for postmaster crash restarts.
Previous Message Alexander Korotkov 2017-09-28 11:22:18 Re: GSoC 2017: weekly progress reports (week 8)