Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification - Mailing list pgsql-hackers

On Thu, Jul 20, 2017 at 7:40 AM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp> wrote:
> Here is an example for $subject using postgres_fdw:
>
> postgres=# create foreign table foreign_tbl (a int, b int) server loopback
> options (table_name 'base_tbl');
> CREATE FOREIGN TABLE
> postgres=# create view rw_view as select * from foreign_tbl where a < b with
> check option;
> CREATE VIEW
> postgres=# insert into rw_view values (0, 10);
> INSERT 0 1
> postgres=# explain verbose update rw_view set a = 20 where b = 10;
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Update on public.foreign_tbl  (cost=100.00..146.21 rows=4 width=14)
>    ->  Foreign Update on public.foreign_tbl  (cost=100.00..146.21 rows=4
> width=14)
>          Remote SQL: UPDATE public.base_tbl SET a = 20 WHERE ((a < b)) AND
> ((b = 10))
> (3 rows)
>
> postgres=# update rw_view set a = 20 where b = 10;
> UPDATE 1
>
> This is wrong!  This should fail.  The reason for that is; direct modify is
> overlooking checking WITH CHECK OPTION constraints from parent views.  I
> think we could do direct modify, even if there are any WITH CHECK OPTIONs,
> in some way or other, but I think that is a feature.  So, I'd like to
> propose to fix this by just giving up direct modify if there are any WITH
> CHECK OPTIONs.  Attached is a patch for that.  I'll add it to the next
> commitfest.

I think that's reasonable.  This should be committed and back-patched
to 9.6, right?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company